#!/usr/bin/perl
#
# Add Audio info to table
#
# Use:
# ./InsertAudioData.pl [--datapath <DataPath>] [--tablename <name>] [TierName1 Tiername2...]
#
# --datapath <DataPath> (e.g., ~/NewSLspeech) for an alternative 
#   source of table files (NOTE: uses <DataPath>/Audio/Tiename 
#   as the actual paths)
#
# --tablename <name> (e.g., 'New') Name added to the conventional table
#   name to distinguish it from the "standard" one.
#
# This will read in all tablefiles from $DatabaseTables/Audio/<TierName>
#
###############################################################################
#
# Copyright R.J.J.H. van Son © 2000, 2001
#
# Author Rob van Son
# Institute of Phonetic Sciences & ACLC
# University of Amsterdam
# Herengracht 338
# NL-1016CG Amsterdam, The Netherlands
# Email: Rob.van.Son@hum.uva.nl
#        rob.van.son@workmail.com
# WWW  : http://www.fon.hum.uva.nl/rob/
# mail:  Institute of Phonetic Sciences
#        University of Amsterdam
#        Herengracht 338
#        NL-1016CG Amsterdam
#        The Netherlands
#        tel +31 205252183
#        fax +31 205252197
#
# License for use and disclaimers
#
# 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";

# Remove password for writing access
$DBMSpassword = '';
require "$HomeDir/connectDBD.pl";


# Use alternate datapaths
if(grep(/\-\-datapath/i, @ARGV))
{
	my $i;
	for($i=0; $i < $#ARGV; ++$i)
	{
		if($ARGV[$i] =~ /\-\-datapath/i)
		{
			$DatabaseTables = $ARGV[$i+1];
			splice(@ARGV, $i, 2);
			
		};
	};
};

my $AlternateTableName = "";
if(grep(/\-\-tablename/i, @ARGV))
{
	my $i;
	for($i=0; $i < $#ARGV; ++$i)
	{
		if($ARGV[$i] =~ /\-\-tablename/i)
		{
			$AlternateTableTableName = $ARGV[$i+1];
			splice(@ARGV, $i, 2);
			
		};
	};
};

my $Target;

my $Tier;
my ($MaxPos, $MinPos, @SamplePos) = (0,0);
# Default is all
@ARGV=('FormantsF1', 'FormantsF2', 'FormantsF3', 'cog') unless @ARGV;
foreach $Tier (@ARGV)
{    
    # Kludge to handle Formants
    my $TablePrefix = ${Tier};
	my $InputPrefix = ${Tier};
    if($Tier =~ /(Formants)(F\d)$/)
    {
		$Tier = $1;
    	$TablePrefix = $2;
		$InputPrefix = $Tier.$TablePrefix
    };
    
    foreach $Target ('_fm', '_hm')
    {
		my $DatabaseCreated = 0;

		my $InputName = "$AlternateTableTableName$InputPrefix$Target";
		my $TableName = "$AlternateTableTableName$TablePrefix$Target";

		my @Columns = ();
		my @ValueTypes = ();
print "$DatabaseTables/Audio/${Tier}/*$InputName.txt.gz\n";
		my @FileList = glob("$DatabaseTables/Audio/${Tier}/*$InputName.txt.gz");
		foreach $FilePath (@FileList)
		{
	    	# Store ID's already handled
    	    my %IdTable = ();

	    	# Open  table with phoneme information
	    	open(INPUT, "gunzip -qc $FilePath|") || die "gunzip -qc $FilePath|: $!\n";

	    	# Get the first line
	    	my $Header = <INPUT>;
	    	chomp($Header);
	    	# Remove leading #
	    	$Header =~ s/^\s*\#[\>]?\s*//g;

	    	# Skip comments
	    	while(($Line = <INPUT>) =~ /^\s*(\#[\>]?\s*|$)/)
	    	{ 
				if($Line =~ /^\s*\#[\>]\s*/)
				{
		    		$Header .= "\t$'";
		    		chomp($Header);
				};
	    	};

	    	unless($DatabaseCreated)
	    	{
				# Remove old table
				$result = $conn->do("DROP TABLE $TableName");

				# Create new table	
				# Column names
				@Columns = split(/[\t]+/, $Header);
				@ValueTypes = map {/\[([^\]]+)\]/; $1;} @Columns;
				@Columns = map {/\s+\[([^\]]+)\]/i; $`;} @Columns;

				# Add BEST column
				push(@Columns, "Best");
				push(@ValueTypes, "FLOAT");

				# Construct table definition for CREATE
				my $i;
				$ColumnDef = "";

				for($i=0; $i < scalar(@Columns); ++$i)
				{
		    		$Columns[$i] = "EndTime" if $Columns[$i] eq 'End';
		    		$ColumnDef .= "$Columns[$i] $ValueTypes[$i], \n";

		    		# Store positions of values
		    		if($Columns[$i] =~ /Max/i)
		    		{
		    			$MaxPos = $i;
		    		}
		    		elsif($Columns[$i] =~ /Min/i)
		    		{
		    			$MinPos = $i;
		    		}
		    		elsif($Columns[$i] =~ /([\d]+)$/i)
		    		{
		    			$SamplePos[$1] = $i;
		    		};

				};
				$ColumnDef =~ s/\,\s*$//g;

				# Create table
				$result = $conn->do("CREATE TABLE $TableName ($ColumnDef);");

				print STDERR $conn->errstr, 
				": CREATE TABLE $TableName ($ColumnDef);\n" if $conn->err;

				$DatabaseCreated = scalar(@Columns);
	    	};

	    	# Fill Table (NOTE: the first line has already been read!)
	    	while($Line)
	    	{
				# Skip comments and empty lines
				($Line = <INPUT>, next) if $Line =~ /^\s*(\#|$)/;
				chomp($Line);
				$Line =~ s/\s+$//g;

				my @Values = split(/\t/, $Line);

				# Get the "BEST" value: That is, the maximum if there is a maximum, 
				# the minimum if there is a minimum, else pick the center value.
				my $MaxVal = $Values[$MaxPos];
				my $MinVal = $Values[$MinPos];
				my @SampledVal = ();
				my $BestValue;
				for($i=0; $i < scalar(@SamplePos); ++$i)
				{
					$SampleVal[$i] = $Values[$SamplePos[$i]];
				};
				my @Signs = ();
				for($i=0; $i < $#SampleVal; ++$i)
				{
					$Sign = $SampleVal[$i] <= $SampleVal[$i+1] ? +1 : -1;
					push(@Signs, $Sign);
				};
				my $CountMaxima = 0;
				my $CountMinima = 0;
				for($i=0; $i < $#Signs; ++$i)
				{
					++$CountMaxima if $Signs[$i] > $Signs[$i+1];
					++$CountMinima if $Signs[$i] < $Signs[$i+1];
				};
				# Select best value
				# There is a single maximum
				if($CountMaxima == 1 && $CountMinima <= 0)
				{
		    		$BestValue = $MaxVal;
				}
				# There is a single minimum
				elsif($CountMaxima <= 0 && $CountMinima == 1)
				{
		    		$BestValue = $MinVal;
				}
				# There is no definite extreme, pick the center value
				else
				{
		    		if(scalar(@SampleVal) % 2)
		    		{
		    			my $CenterPos = $#SampleVal/2;
		    			$BestValue = $SampleVal[$CenterPos];
		    		}
		    		else
		    		{
		    			my $CenterPos = $#SampleVal/2;
		    			$BestValue = ($SampleVal[$CenterPos] + $SampleVal[$CenterPos+1])/2.0;
		    		};
				};
				push(@Values, $BestValue);

				# Add '-quotes to char values
				my $InsertList = "";
				my $i;
				for($i=0; $i < scalar(@Values); ++$i)
				{
		    		$Values[$i] =~ s/\'/\\\'/g;
		    		$Values[$i] = "E'$Values[$i]'" if($ValueTypes[$i] =~ /char|text/i && $Values[$i] ne 'NULL');
				};

				# Read next line
				$Line = <INPUT>;

				# Make sure NO values are entered that do NOT have a the first two entries
		    	next unless $Values[0] && $Values[1];

				if(scalar(@Values) != $DatabaseCreated)
				{
		    		print STDERR "ERROR: ", scalar(@Values), " found where $DatabaseCreated expected\n";
		    		print STDERR join("\t", @Columns), "\n";
		    		print STDERR join("\t", @Values), "\n";

		    		next;
				};

                # Do not process double entries! (supposes that the 0 value is the unit ID)
                next if ++$IdTable{$Values[0]} > 1;

				# Construct the insert list
				my $InsertList = join(", ", @Values);

				# Insert the record
				$result = $conn->do("INSERT INTO $TableName VALUES ($InsertList);");

				if($conn->err)
				{
		    		print STDERR $conn->errstr, 
		    		": INSERT INTO $TableName VALUES ($InsertList);\n";

		    		while(@Values || @ValueTypes)
		    		{
						print shift(@Values), "\t", shift(@ValueTypes), "\n";
		    		};

		    		die "$ColumnDef\n";
				}; 
	    	};

		};
	
		# Create indices on ID
		$result = $conn->do("CREATE INDEX ${TableName}_index ON $TableName (id);");

		print STDERR $conn->errstr, 
	    	": create index ${TableName}_index on $TableName (id);\n" if $conn->err;

        # Grant SELECT to user ANONYMOUS
        $conn->do("GRANT SELECT on $TableName TO PUBLIC");
        print "GRANT SELECT on $TableName TO PUBLIC: ",$conn->errstr, "\n";
	
		# Test data
        my @ary = $conn->selectrow_array("select count(id) from $TableName");

        print "Query $TableName: ",$conn->errstr, " ", $ary[0], "\n";
    };
};
