#!/usr/bin/perl -w
use strict;
use DBI();
use Getopt::Long;
use File::Basename;



# configuration
#my $channelsconf = "/root/.szap/channels.conf";
#my $channelsconf = "/usr/src/DVB/apps/szap/channels.conf-dvbt-oxford";
my $channelsconf = "channels.conf";

# type of channels.conf file
# for now szap or tzap
my $channelsconftype = "tzap";

my $satid = 1;
my $inversion="a";
my $sourceid = 1;

# wether to ask for or not
my $ask_transportid = 1;
my $ask_networkid = 1;
my $ask_serviceid = 1;
my $ask_tpid = 1;
my $ask_modulation = 1;
my $sql1="Invalid";

# do nothing
my $simulate = 0;


# variable definition
my $doinserts;
my $dofind;
my $networkid;
my $transportid;
my @channeldata;
my %channelhash;
my $db_host;
my $db_user;
my $db_name;
my $db_pass;



# Read the mysql.txt file in use by MythTV.
# could be in a couple places, so try the usual suspects
        open(CONF, "/usr/share/mythtv/mysql.txt")
                or open(CONF, "/usr/local/share/mythtv/mysql.txt")
                or die ("Unable to open /usr/share/mythtv/mysql.txt:  $!\n\n");
        while (my $line = <CONF>) {
                chomp($line);
                $line =~ s/^str //;
                my ($var, $val) = split(/\=/, $line, 2);
                next unless ($var && $var =~ /\w/);
                if ($var eq 'DBHostName') {
                        $db_host = $val;
                }
                elsif ($var eq 'DBUserName') {
                        $db_user = $val;
                }
                elsif ($var eq 'DBName') {
                        $db_name = $val;
                }
                elsif ($var eq 'DBPassword') {
                        $db_pass = $val;
                }
        }
        close CONF;

# Connect to the database
        my $dbh = DBI->connect("dbi:mysql:database=$db_name:host=$db_host", $db_user, $db_pass)
                or die "Cannot connect to database: $!\n\n";

if ($simulate) {
            print "Simulation mode. SQL not done.\n";
        };

# Now retrieve data from the channel table.
my $tb_channel = $dbh->prepare("SELECT * FROM channel WHERE sourceid=$sourceid");
$tb_channel->execute();
while (my $ref = $tb_channel->fetchrow_hashref()) {
  $dofind = 0;
  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) {
    # channel already exists in dvb_channel
    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: Less than 2 dvb_pids not found\n";
    
    }
    print "Do you want to re-insert? [n]/y:";
    my $selection = <>;
    chomp($selection);
    if ($selection eq "y" ) {
      $dofind = 1 ;
     }
  $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";
    $dofind = 1;
}

if ($dofind) {
    open (CHANNELSCONF, "cat $channelsconf | grep -i \"" . $ref->{'name'} . "\" |") or die "failed opening channels.conf: $!";
    my @channelsconf = <CHANNELSCONF>;
    close CHANNELSCONF;
    
    my $channelsconfrows=$#channelsconf + 1;

    if ( $channelsconfrows == 0 ) {
     print "Zero rows found. Do you want to enter a search term? [n]/y:";
    my $selection = <>;
    chomp($selection);
    if ($selection eq "y" ) {
        print "Enter search term:";
        $selection = <>;
        chomp($selection);

        open (CHANNELSCONF, "cat $channelsconf | grep -i \"" . $selection . "\" |") or die "failed opening channels.conf: $!";
        @channelsconf = <CHANNELSCONF>;
        close CHANNELSCONF;
         $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]);
        
        # extract the data out of the channelsconf row
        if ($channelsconftype eq 'szap') {
            $channelhash{'frequency'} = $channeldata[1] . '000';
            $channelhash{'polarity'} = $channeldata[2];
            $channelhash{'satid'} = $channeldata[3];
            $channelhash{'symbolrate'} = $channeldata[4] . '000';
            $channelhash{'vpid'} = $channeldata[5];
            $channelhash{'apid'} = $channeldata[6];
            $channelhash{'serviceid'} = $channeldata[8];
            
            # on dvb-s we always have qpsk
            $channelhash{'modulation'} = 'qpsk';
	} elsif ($channelsconftype eq 'tzap') {
	    $channelhash{'frequency'} = $channeldata[1];
	    $channelhash{'inversion'} = $channeldata[2];
	    $channelhash{'bandwidth'} = $channeldata[3];
	    $channelhash{'fec_hp'} = $channeldata[4];
	    $channelhash{'fec_lp'} = $channeldata[5];
	    $channelhash{'modulation'} = $channeldata[6];
	    $channelhash{'transmissionmode'} = $channeldata[7];
	    $channelhash{'guardinterval'} = $channeldata[8];
	    $channelhash{'hierarchy'} = $channeldata[9];
	    $channelhash{'vpid'} = $channeldata[10];
	    $channelhash{'apid'} = $channeldata[11];
	    $channelhash{'tpid'} = $channeldata[12] if defined($channeldata[12]);

            $channelhash{'bandwidth'} =~ s/BANDWIDTH_//;
            $channelhash{'bandwidth'} =~ s/_.*//;

	    # make modulation lowercase
	    $channelhash{'modulation'} =~ s/QAM/qam/g;

	    $channelhash{'fec_hp'} =~ s/FEC_//g;
	    $channelhash{'fec_hp'} =~ s/_/\//g;    

	    $channelhash{'fec_lp'} =~ s/FEC_//g;
	    $channelhash{'fec_lp'} =~ s/_/\//g;    

	    $channelhash{'guardinterval'} =~ s/GUARD_INTERVAL_//g;
	    $channelhash{'guardinterval'} =~ s/_/\//g;

	    $channelhash{'tpid'} =~ s/$/ /g;

            chomp($channelhash{'apid'});
            
            # Set polarity and symbolrate here to defaults
            $channelhash{'polarity'} = 'V';
            $channelhash{'symbolrate'} = '69000';


	    $channelhash{'transmissionmode'} =~ s/TRANSMISSION_MODE_//g;
            $channelhash{'transmissionmode'} =~ s/K//g; 
                
	    if ($channelhash{'inversion'} eq "INVERSION_OFF" ) {
		$channelhash{'inversion'} = '0' } else {
		$channelhash{'inversion'} = '1'
	    };

	    if ($channelhash{'hierarchy'} eq "HIERARCHY_NONE" ) {
		$channelhash{'hierarchy'} = 'n' } else {
                $channelhash{'hierarchy'} = 'auto'
	    }
 
	    $ask_transportid = 0;
	    $ask_networkid = 0;
	    $ask_serviceid = 0;
        }
        
        
        print "fec_hp: " .    $channelhash{'fec_hp'} . "\n";
        print "fec_lp: " .    $channelhash{'fec_lp'}  . "\n";
        print "inversion: " .     $channelhash{'inversion'} . "\n";
        print "transmissionmode: " .     $channelhash{'transmissionmode'} . "\n";
        print "guardinterval: " .    $channelhash{'guardinterval'}  . "\n";
        print "hierarchy: " .   $channelhash{'hierarchy'}  . "\n";
        print "chanid: " . $ref->{'chanid'} . "\n";
        print "frequency: $channelhash{'frequency'}\n";
        print "polarity: $channelhash{'polarity'}\n";
        print "symbolrate: $channelhash{'symbolrate'}\n";
        print "modulation: $channelhash{'modulation'}\n";
        print "VPID: $channelhash{'vpid'}\n";
        print "APID: $channelhash{'apid'}\n";
        
        if ( (($channelsconftype eq 'tzap') and defined($channeldata[12])) or ($ask_tpid == 0) ) {
            if ($ask_tpid == 0) {
                $channelhash{'tpid'} = 0;
                print "WARNING: tpid will not be inserted in dvb_pids.\n";
            }
            print "TPID: $channelhash{'tpid'}\n";       
        } else {
            print "TPID: ";
            $channelhash{'tpid'}=<>;
            chomp ($channelhash{'tpid'});        
        }
        
        if (($ask_serviceid) and ($channelsconftype eq 'tzap')) {
            # ask for serviceid if $ask_serviceid is set and we don't have it from the channels.conf
            print "serviceid: ";
            $channelhash{'serviceid'}=<>;
            chomp ($channelhash{'serviceid'});
        } else {
            if ($channelsconftype eq 'tzap') {
                # set a default value if we don't have it from channels.conf and $ask_serviceid is not set
                $channelhash{'serviceid'} = 0;
            }
            print "serviceid: $channelhash{'serviceid'}\n";
        }

        
        if ($ask_transportid) {        
            print "transportid: ";
            $channelhash{'transportid'}=<>;
            chomp ($channelhash{'transportid'});
        } else {
            $channelhash{'transportid'} = '0';
            print "transportid: $channelhash{'transportid'}\n";
        }
        
        if ($ask_networkid) {
            print "networkid: ";
            $channelhash{'networkid'}=<>;
            chomp ($channelhash{'networkid'});
        } else {
            $channelhash{'networkid'} = '0';
            print "networkid: $channelhash{'networkid'}\n";
        }
        
        $doinserts=1;
        
    } elsif ($selection eq "m") {
        # enter information manually
        print "chanid: " . $ref->{'chanid'} . "\n";
        
        print "frequency: ";
        $channelhash{'frequency'}=<>;
        chomp($channelhash{'frequency'});
       
        if ($channelsconftype eq 'szap') {
            print "polarity (lowercase): ";
            $channelhash{'polarity'}=<>;
            chomp($channelhash{'polarity'});
        
            print "symbolrate (5 digits): ";
            $channelhash{'symbolrate'}=<>;
            chomp($channelhash{'symbolrate'});
            # adjust data for use with mythtv
            $channelhash{'symbolrate'} = $channelhash{'symbolrate'} . "000";
        } else {
            # we don't need this for dvb-t
            $channelhash{'polarity'} = '';
            $channelhash{'symbolrate'} = 0;
        }        

        if ($ask_modulation) {
            print "modulation (qpsk, qam_xx): ";
            $channelhash{'modulation'}=<>;
            chomp ($channelhash{'modulation'});
        } else {
            $channelhash{'modulation'} = '';
            print "modulation: $channelhash{'modulation'}\n";
        }

        print "VPID: ";
        $channelhash{'vpid'}=<>;
        chomp($channelhash{'vpid'});

        print "APID: ";
        $channelhash{'apid'}=<>;
        chomp($channelhash{'apid'});

        if ($ask_tpid) {
            print "TPID: ";
            $channelhash{'tpid'}=<>;
            chomp ($channelhash{'tpid'});
        } else {
            $channelhash{'tpid'} = '0';
            print "TPID (will not be inserted in dvb_pids): $channelhash{'tpid'}\n";
        }
                
        if ($ask_serviceid) {
            print "serviceid: ";
            $channelhash{'serviceid'}=<>;
            chomp ($channelhash{'serviceid'});
        } else {
            $channelhash{'serviceid'} = '0';
            print "serviceid: $channelhash{'serviceid'}\n";
        }
        
        if ($ask_transportid) {        
            print "transportid: ";
            $channelhash{'transportid'}=<>;
            chomp ($channelhash{'transportid'});
        } else {
            $channelhash{'transportid'} = '0';
            print "transportid: $channelhash{'transportid'}\n";
        }
        
        if ($ask_networkid) {
            print "networkid: ";
            $channelhash{'networkid'}=<>;
            chomp ($channelhash{'networkid'});
        } else {
            $channelhash{'networkid'} = '0';
            print "networkid: $channelhash{'networkid'}\n";
        }


        $doinserts=1;
    } else {
        $doinserts=0;
    }

    if ($doinserts==1) {
        # insert1: dvb_channel

if ($channelsconftype eq 'szap') {
         $sql1 = "REPLACE INTO dvb_channel (chanid, serviceid, networkid, transportid, frequency, inversion, symbolrate, polarity, satid, modulation) " . 
 "VALUES (" . $ref->{'chanid'} . ", $channelhash{'serviceid'}, $channelhash{'networkid'}, $channelhash{'transportid'}, " .
                   "$channelhash{'frequency'}, \'$inversion\', $channelhash{'symbolrate'}, \'$channelhash{'polarity'}\', $satid, \'$channelhash{'modulation'}\');";

} 

if ($channelsconftype eq 'tzap') {


	print "channel type" . $channelsconftype . "\n";
	$sql1 = "REPLACE INTO dvb_channel (chanid, serviceid, networkid" .
		", providerid, transportid, frequency, inversion, symbolrate" .
		", fec, polarity, satid, modulation, bandwidth, lp_code_rate" .
		", transmission_mode, guard_interval, hierarchy, pmtcache) " .
		"VALUES (" . $ref->{'chanid'} . ", $channelhash{'serviceid'}" .
		", $channelhash{'networkid'}, 0" .
		", $channelhash{'transportid'}, $channelhash{'frequency'}" .
		", \'$inversion\', \'$channelhash{'symbolrate'}\'" .
		", \'$channelhash{'fec_lp'}\', \'$channelhash{'polarity'}\'" .
		", 0, \'$channelhash{'modulation'}\'" .
		", \'$channelhash{'bandwidth'}\', \'$channelhash{'fec_lp'}\'" .
		", \'$channelhash{'transmissionmode'}\'" .
		", \'$channelhash{'guardinterval'}\'" .
		", \'$channelhash{'hierarchy'}\', NULL);";

}

        #insert2: vpid
        my $sql2 = "REPLACE INTO dvb_pids (chanid, pid, type) VALUES (" . $ref->{'chanid'} . ", $channelhash{'vpid'}, \'v\');"; 

        #insert3: apid
        my $sql3 = "REPLACE INTO dvb_pids (chanid, pid, type) VALUES (" . $ref->{'chanid'} . ", $channelhash{'apid'}, \'a\');"; 

        #insert4: tpid
        my $sql4 = "REPLACE INTO dvb_pids (chanid, pid, type) VALUES (" . $ref->{'chanid'} . ", $channelhash{'tpid'}, \'t\');"; 

                    
        print "Generated SQL sql1: \n$sql1\n";
        print "Generated SQL sql2: \n$sql2\n";
        print "Generated SQL sql3: \n$sql3\n";
        print "Generated SQL sql4: \n$sql4\n";
        
        
        if ($simulate) {
            print "Simulation mode. SQL not done.\n";
        } else {
            $dbh->do($sql1);
            $dbh->do($sql2);
            $dbh->do($sql3);
            
            if ($ask_tpid == 1) {
                $dbh->do($sql4);
            }
            
            print "SQL insert complete.\n";
        }
    
    }

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

$dbh->disconnect();