#!/usr/bin/perl -w
use strict;
use DBI();

my $channelsconf = "/root/.szap/channels.conf";
my $satid = 1;
my $inversion="a";


#variable definition
my $doinserts;
my $networkid;
my $transportid;
my @channeldata;

# Connect to the database.
my $dbh = DBI->connect("DBI:mysql:database=mythconverg;host=localhost",
                       "mythtv", "mythtv",
                       {'RaiseError' => 1});

# Now retrieve data from the channel table.
my $tb_channel = $dbh->prepare("SELECT * FROM channel");
$tb_channel->execute();
while (my $ref = $tb_channel->fetchrow_hashref()) {
  print "\nFound a row: chanid = $ref->{'chanid'}, name = $ref->{'name'}\n";
  
  # search corresponding rows in dvb_channel
  my $tb_dvb_channel = $dbh->prepare("SELECT * FROM dvb_channel WHERE chanid=" . $ref->{'chanid'});
  my $count_dvbchannel = $tb_dvb_channel->execute();
  
  if ($count_dvbchannel >= 1) {
    print "Found $count_dvbchannel corresponding row(s) in dvb_channel table\n";
    
    # integrity check: there have to be at least two rows in dvb_pids
    my $tb_dvb_pids = $dbh->prepare("SELECT * FROM dvb_pids WHERE chanid=" . $ref->{'chanid'});
    my $count_dvbpids = $tb_dvb_pids->execute();
    
    if ($count_dvbpids != 2) {
        print "Warning: 2 dvb_pids not found\n";
    }
    
    $tb_dvb_pids->finish();  
  
  } else {
    # no corresponding row in dvb_channel, so let's insert data
    print "no corresponding row in dvb_channel, need data, searching channels.conf\n";
    
    open (CHANNELSCONF, "cat $channelsconf | grep -i \"" . $ref->{'name'} . "\" |") or die "failed opening channels.conf: $!";
    my @channelsconf = <CHANNELSCONF>;
    close CHANNELSCONF;
    
    my $channelsconfrows=$#channelsconf + 1;
    
    print "found $channelsconfrows matching rows in channels.conf\n";
    
    for (my $i = 1; $i < $channelsconfrows + 1; $i++) {
        print "$i: " . $channelsconf[$i-1] . "\n";
    }

    print "What to do [number (use one of the above), i (ignore), m (manually enter)]? ";
    my $selection = <>;
    chomp($selection);
    
    print $selection . "\n";
    
    if ($selection =~ /^\d+$/) {
        print "using row $selection\n";
        
        # get the information out of the channelsconf row
        @channeldata=split(/:/, $channelsconf[$selection-1]);
        
        #adjust data for use with mythtv
        $channeldata[1] = $channeldata[1] . "000";
        $channeldata[4] = $channeldata[4] . "000";
	$channeldata[7] = "null";
	$channeldata[8] = "null";

        
        print "chanid: " . $ref->{'chanid'} . "\n";
        print "frequency: $channeldata[1]\n";
        print "polarity: $channeldata[2]\n";
        print "symbolrate: $channeldata[4]\n";
        print "VPID: $channeldata[5]\n";
        print "APID: $channeldata[6]\n";
        print "serviceid: $channeldata[8]\n";
        #print "transportid: ";
        #$transportid=<>;
        #chomp ($transportid);
	$transportid = "null";
        #print "networkid: ";
        #$networkid=<>;
        #chomp ($networkid);
	$networkid="null";
        
        $doinserts=1;
        
    } elsif ($selection eq "m") {
        # enter information manually
        print "chanid: " . $ref->{'chanid'} . "\n";
        
        print "frequency (5 digits): ";
        $channeldata[1]=<>;
        chomp($channeldata[1]);
        #adjust data for use with mythtv
        $channeldata[1] = $channeldata[1] . "000";

        print "polarity (lowercase): ";
        $channeldata[2]=<>;
        chomp($channeldata[2]);
        
        print "symbolrate (5 digits): ";
        $channeldata[4]=<>;
        chomp($channeldata[4]);
        #adjust data for use with mythtv
        $channeldata[4] = $channeldata[4] . "000";        

        print "VPID: ";
        $channeldata[5]=<>;
        chomp($channeldata[5]);

        print "APID: ";
        $channeldata[6]=<>;
        chomp($channeldata[6]);
                
        print "serviceid: ";
        $channeldata[8]=<>;
        chomp($channeldata[8]);

        print "transportid: ";
        $transportid=<>;
        chomp ($transportid);
        
        print "networkid: ";
        $networkid=<>;
        chomp ($networkid);
                
        
        
        $doinserts=1;
    } else {
        $doinserts=0;
    }

    if ($doinserts==1) {
        # insert1: dvb_channel
        my $sql1 = "INSERT INTO dvb_channel (chanid, serviceid, networkid, transportid, frequency, inversion, symbolrate, polarity, satid) " . 
                   "VALUES (" . $ref->{'chanid'} . ", $channeldata[8], $networkid, $transportid, $channeldata[1], \'$inversion\', $channeldata[4], \'$channeldata[2]\', $satid);";
        
        #insert2: vpid
        my $sql2 = "INSERT INTO dvb_pids (chanid, pid, type) VALUES (" . $ref->{'chanid'} . ", $channeldata[5], \'v\');"; 

        #insert3: apid
        my $sql3 = "INSERT INTO dvb_pids (chanid, pid, type) VALUES (" . $ref->{'chanid'} . ", $channeldata[6], \'a\');"; 
                    
        print "Generated SQL sql1: \n$sql1\n";
        print "Generated SQL sql2: \n$sql2\n";
        print "Generated SQL sql3: \n$sql3\n";
        
        
        $dbh->do($sql1);
        $dbh->do($sql2);
        $dbh->do($sql3);
        print "SQL insert complete.\n";
    
    }

  }
  
  $tb_dvb_channel->finish();
  
}
$tb_channel->finish();

$dbh->disconnect();