#!/usr/bin/perl
# Copy the names and listings from some channels to other channels.
# This is useful if, for example, you've got a machine in the UK with both
# DVB-T and DVB-S sources; the only source of listings for the radio channels
# is the DVB-T EPG.
# Adam Sampson <ats@offog.org>

use DBI;

my $dbh = DBI->connect("DBI:mysql:db=mythconverg", "mythtv", "oxxzqsve",
                       { RaiseError => 1, AutoCommit => 0 });

my %chanid_map = ();
my %chanid_name = ();
my %chanid_callsign = ();
my %chanid_xmltvid = ();

# Source 3 is my DVB-T card.
my $selh = $dbh->prepare("SELECT a.chanid, b.chanid, a.name, a.callsign, b.xmltvid FROM channel a, channel b WHERE a.chanid != b.chanid AND a.channum = b.channum AND b.sourceid = 3");
$selh->execute;
while (my $row = $selh->fetch) {
	$chanid_map{$row->[0]} = $row->[1];
	$chanid_name{$row->[0]} = $row->[2];
	$chanid_callsign{$row->[0]} = $row->[3];
	$chanid_xmltvid{$row->[1]} = $row->[4];
}
$selh->finish;

foreach my $src (sort keys %chanid_map) {
	my $dest = $chanid_map{$src};

	my $uph = $dbh->prepare("UPDATE channel SET name = ?, callsign = ? WHERE chanid = ?");
	$uph->execute($chanid_name{$src}, $chanid_callsign{$src}, $dest);
	$uph->finish;

	next if $chanid_xmltvid{$dest} ne '';

	my $delh = $dbh->prepare("DELETE FROM program WHERE chanid = ?");
	$delh->execute($dest);
	$delh->finish;

	$selh = $dbh->prepare("SELECT * FROM program WHERE chanid = ?");
	$selh->execute($src);

	my $count = 0;
	while (my $row = $selh->fetchrow_hashref) {
		my @cols = keys %$row;

		$row->{chanid} = $dest;

		my $insh = $dbh->prepare("INSERT INTO program (" . join(", ", @cols) . ") VALUES (" . join(", ", map { '?' } @cols) . ")");
		$insh->execute(map { $row->{$_} } @cols);
		$insh->finish;

		$count++;
	}
	print "Copied $count programmes from $src ($chanid_name{$src}) to $dest.\n";

	$selh->finish;
}

$dbh->commit;

system("./mythreschedule")