#! /usr/bin/perl
#
#    
# Copyright (C) 2002  R.J.J.H. van Son
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
# 
#
$HomeDir = '../../../../';
require "$HomeDir/Links.pl";
# Connect to database
#require "$HomeDir/connect.pl";
#
my $TargetDirectory = "../DatabaseFiles/scripts/SQL";

my %ParameterTable = (
'cog' => 'cog',
'Pitch' => 'F0', 
'F1' => 'F1', 
'F2' => 'F2', 
'F3' => 'F3'
);

my @CompressionList = ('Smd', 'Ogg40', 'Ogg', 'MP3', 'SOM');
push(@CompressionList, @ARGV);

my @AnalysisList = ('cog', 'Pitch', 'F1', 'F2', 'F3');
my $AnalysisParameter;
foreach $AnalysisParameter (@AnalysisList)
{
	my $TableName = $AnalysisParameter;
	my $ParameterName = $ParameterTable{${AnalysisParameter}} || ${AnalysisParameter};
	my $SourceName = $AnalysisParameter;
	
	# Initialize the SELECT components
	my $FMvalueList = "${AnalysisParameter}_fm.time2\tAS ${ParameterName}_fm";
	my $HMvalueList = "${AnalysisParameter}_hm.time2\tAS ${ParameterName}_hm";
	my $FMdiffList = "${SourceName}_fm.time2 - ${SourceName}_hm.time2\tAS ${ParameterName}diff";
	my $HMdiffList = "";
	my $FMsemiList = "0.0\tAS ${ParameterName}semi";
	my $HMsemiList = "";
	my $FMtablelist = "${AnalysisParameter}_fm";
	my $HMtablelist = "${AnalysisParameter}_hm";
	my $SynchronizeWhereList = "${SourceName}_fm.id = ${SourceName}_hm.id\n"
	                           ."\tAND ${SourceName}_hm.id = ${SourceName}_fm.id";
	my $FMUpdateList =<< "UPDATELISTSTRINGINITEND";
UPDATE 
	compress${TableName}diff
SET 
	${ParameterName}semi = 12*(ln(${ParameterName}_fm) - ln(${ParameterName}_hm))/ln(2.0)
WHERE
	${ParameterName}_fm > 0 AND ${ParameterName}_hm > 0
;

UPDATELISTSTRINGINITEND
	my $HMUpdateList = "";


	
	my $CompressionType;
	foreach $CompressionType (@CompressionList)
	{
		# The components of the SELECT INTO query
		$FMvalueList .= ",\n\t$CompressionType${SourceName}_fm.time2\tAS $CompressionType${ParameterName}_fm";
		$HMvalueList .= ",\n\t$CompressionType${SourceName}_hm.time2\tAS $CompressionType${ParameterName}_hm";
		$FMdiffList .= ",\n\t${SourceName}_fm.time2 - $CompressionType${SourceName}_fm.time2\tAS $CompressionType${ParameterName}diff_fm";
		$HMdiffList .= ",\n\t${SourceName}_hm.time2 - $CompressionType${SourceName}_hm.time2\tAS $CompressionType${ParameterName}diff_hm";
		$FMsemiList .= ",\n\t0.0\tAS $CompressionType${ParameterName}semi_fm";
		$HMsemiList .= ",\n\t0.0\tAS $CompressionType${ParameterName}semi_hm";
		$FMtablelist .= ", $CompressionType${SourceName}_fm";
		$HMtablelist .= ", $CompressionType${SourceName}_hm";
		$SynchronizeWhereList .= "\n\tAND ${SourceName}_fm.id = $CompressionType${SourceName}_fm.id\n"
		                       ."\tAND ${SourceName}_hm.id = $CompressionType${SourceName}_hm.id";

		# The Update list.
		$FMUpdateList .=<< "FMUPDATELISTSTRINGEND";
UPDATE 
	compress${TableName}diff
SET 
	$CompressionType${ParameterName}semi_fm = 12*(ln(${ParameterName}_fm) - ln($CompressionType${ParameterName}_fm))/ln(2.0)
WHERE
	${ParameterName}_fm > 0 AND $CompressionType${ParameterName}_fm > 0
;

FMUPDATELISTSTRINGEND

		$HMUpdateList .=<< "HMUPDATELISTSTRINGEND";
UPDATE 
	compress${TableName}diff
SET 
	$CompressionType${ParameterName}semi_hm = 12*(ln(${ParameterName}_hm) - ln($CompressionType${ParameterName}_hm))/ln(2.0)
WHERE
	${ParameterName}_hm > 0 AND $CompressionType${ParameterName}_hm > 0
;

HMUPDATELISTSTRINGEND

	};
	# Clean up incorrect strings
	$HMdiffList =~ s/^\s*\,\s*//g;
	$HMsemiList =~ s/^\s*\,\s*//g;
	
	# Print to SQL file
	open(SQLSCRIPT, ">$TargetDirectory/compress${TableName}diff.sql") 
	|| die ">$TargetDirectory/compress${TableName}diff.sql: $!\n";
	#
	print SQLSCRIPT << "ENDOFSQLSCRIPT";
DROP TABLE compress${TableName}diff;

SELECT 
	${TableName}_fm.id	AS id, 
	$FMvalueList,
	$FMdiffList,
	$FMsemiList,
	$HMvalueList,
	$HMdiffList,
	$HMsemiList
INTO
	compress${TableName}diff
FROM 
	$FMtablelist,
	$HMtablelist
WHERE 
	$SynchronizeWhereList;

CREATE INDEX compress${TableName}diff_index on compress${TableName}diff (id);

GRANT SELECT on compress${TableName}diff TO anonymous;

$FMUpdateList
$HMUpdateList

	
ENDOFSQLSCRIPT
	close(SQLSCRIPT);
};