#!/usr/bin/perl
#
use strict ;

use Linux::DVB::DVBT ;

# Local
use App::Framework '+Sql' ;

use Linux::DVB::DVBT::Apps::QuartzPVR::Config::Constants ;


# VERSION
our $VERSION = '1.001' ;
our $DEBUG=0;
our $APP;

	# Create application and run it
	$APP = App::Framework->new() ;
	$APP->go() ;



#=================================================================================
# SUBROUTINES EXECUTED BY APP
#=================================================================================

#----------------------------------------------------------------------
# Main execution
#
sub app
{
	my ($app, $opts_href) = @_ ;

	# options
	my %opts = $app->options() ;
	$DEBUG=$opts_href->{'debug'} ;

	Linux::DVB::DVBT->debug($DEBUG) ;
	Linux::DVB::DVBT->verbose($opts_href->{'verbose'} ? 2 : 0) ;
	
	# get sql
	my $sql = $app->sql() ;
	

	## Create dvb (use first found adapter). 
	## NOTE: With default object settings, the application will
	## die on *any* error, so there is no error checking in this script
	##
	my $dvb = Linux::DVB::DVBT->new('errmode' => 'return') ;
	
	# read config
	my $tuning_href = $dvb->get_tuning_info() ;
	my $channels_aref = $dvb->get_channel_list() ;
	
	my $user = $opts_href->{'user'}	|| Linux::DVB::DVBT::Apps::QuartzPVR::Config::Constants::SQL_USER ;
	my $password = $opts_href->{'password'}	|| Linux::DVB::DVBT::Apps::QuartzPVR::Config::Constants::SQL_PASSWORD ;
	
	if (!$user || !$password)
	{
		print "Error: You must specify the MySQL username AND password when using this script outside the PVR suite\n" ;
		exit 1 ;
	}
	
	# Set up database
	my %sql_vars ;
	$app->sql->set(
			'database'	=> $opts_href->{'database'},
			'table'		=> $opts_href->{'table'},
			'user'		=> $user,
			'password'	=> $password,
			
			'trace'		=> $opts_href->{'debug'},
			'trace'		=> $opts_href->{'dbg-sql'},
			'trace_file'=> 'logsql.log',
			'debug' 	=> $opts_href->{'dbg-sql'},
			
			'prepare'	=> {

				# get channels 
				'select'		=> {
				},

				'insert'	=> {
					'vars'	=> [qw/channel display_name chan_num chan_type show iplay/],
					'vals'	=> \%sql_vars,
				}, 
			}, # 'prepare'
	) ;

	### Update database ###
	print "\nUpdating database:\n" ;

# TODO: create table if not there already	

	## get current settings
	my %chans_sql ;
	my @chans = $app->sql->sth_query_all('select') ;
	foreach my $chan_href (@chans)
	{
		my $chan = $chan_href->{'channel'} ;
		
		#  	channel  	
		#	display_name 
		#	chan_num 
		#	chan_type 	set('tv', 'radio')
		#	show	
		$chans_sql{$chan} = $chan_href ;	
	}

$app->prt_data("current sql=", \%chans_sql) if $DEBUG ;
	
	# empty table
	$app->sql->do("truncate $opts_href->{'table'}") ;
	

	## work through current scan results
	foreach my $ch_href (@$channels_aref)
	{
		my $chan = $ch_href->{'channel'} ;
		my $display = $chans_sql{$chan}{'display_name'} || $chan ;
		my $show = 1 ;
		$show = $chans_sql{$chan}{'show'} if exists($chans_sql{$chan}{'show'}) ;
		
		my $iplay = 0 ;
		if ($chan =~ /BBC/)
		{
			$iplay = 1 ;
		}
				
		my $href = {
			'channel'		=> $chan,
			'display_name'	=> $display,
			'chan_num'		=> $ch_href->{'channel_num'},
			'chan_type'		=> $ch_href->{'type'},
			'show'			=> $show,
			'iplay'			=> $iplay,
		} ;
		sql_prepare_vals($href, \%sql_vars) ;
		$app->sql->sth_query('insert') ;
		
		print "$chan [$display] $ch_href->{'channel_num'} $ch_href->{'type'} [$show]\n" ;
	}


}



#=================================================================================
# LOCAL SUBROUTINES
#=================================================================================

#---------------------------------------------------------------------------------------------------
# copy contents of hash intop sql vars hash
sub sql_prepare_vals
{
	my ($href, $sql_vars_href) = @_ ;
	
	foreach my $key (keys %$href)
	{
		my $val = $href->{$key} ;
		$sql_vars_href->{$key} = $val ;
	}

$APP->prt_data("sql_prepare_vals() src=", $href, " dest=", $sql_vars_href, "\n") if $DEBUG ;
}



#=================================================================================
# SETUP
#=================================================================================
__DATA__

[SUMMARY]

Updates/creates the channels table 

[OPTIONS]

-db|'database'=s		Database [default=$DEF_DATABASE]

Specify database name

-tbl|'table'=s			Table [default=$DEF_TBL_CHANNELS]

Specify database table name

-u|'user'=s		User

Specify Mysql user name

-p|'password'=s		Password

Specify Mysql user password

-dbg-sql=i	Debug sql module

[DESCRIPTION]

Updates the channels table with the latest list of channel names. Preserves any existing settings.

__DATA__ channels.sql
-- phpMyAdmin SQL Dump
-- version 2.8.0.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Oct 02, 2009 at 08:59 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.9
-- 
-- Database: `tvguide`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `channels`
-- 

CREATE TABLE `channels` (
  `channel` varchar(256) NOT NULL COMMENT 'Channel name used by DVB-T',
  `display_name` varchar(256) NOT NULL COMMENT 'Displayed channel name',
  `chan_num` int(11) NOT NULL auto_increment COMMENT 'Channel number',
  `chan_type` set('tv','radio') NOT NULL default 'tv' COMMENT 'TV or Radio',
  `show` tinyint(1) NOT NULL default '1' COMMENT 'Whether to show this channel or not',
  `iplay` tinyint(1) NOT NULL default '0' COMMENT 'Can the channel be recorded using get_iplayer',
  PRIMARY KEY  (`chan_num`),
  KEY `type_show_num` (`chan_type`,`show`,`chan_num`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
__DATA__ END