#!/usr/bin/perl
package IndexedTable;
use strict;
use warnings;
use SimpleObject;
use SimpleObject;
use Carp qw(croak carp cluck);
use Storable qw(dclone store retrieve);
use Data::Dumper;
use Hash::Merge qw(merge);
##use RDF::Query::Client;
##use Spreadsheet::Read;

our @ISA = qw(SimpleObject);

our $sparql_loaded = 0;

our $useCacheDistinct = 0;

my $useCacheWorkbooks = 0;
my $lastworkbookfile;
my %cacheWorkbooks;

sub set_useCacheWorkbooks {
	$useCacheWorkbooks = $_[1];
}
sub clearCacheWorkbooks {
	%cacheWorkbooks = ();
}

sub create_from_SQL {
	my ($class, $connection, $sqlstring, $numkeys, $id) =  @_; 
	# $connection:	DBI object of open connection
	# $sqlstring:	an sql string resulting in a table
	# $numkeys:	the number of primary key columns (have to be the first columns to be returned from the database)
	# $id:		ID with which the table can be referred to in registries
	
	my $retrieved_array; 
	my @table_header;
	
	require 'DBI.pm';
	
	eval{	#exception handling to avoid fatals
								
		local $SIG{__WARN__}=sub{}; #Temporarily suppress warnings
		my $sth = $connection->prepare($sqlstring);
		$sth->execute();
		@table_header = @{$sth->{NAME}};
		
		$retrieved_array = $sth->fetchall_arrayref(); #temporary array to store query result
		$sth->finish();
	};
	if ($@) {
		croak "$@\n For SQL: $sqlstring\n";
	}
							
	my $hash = create_index_structure($retrieved_array, $numkeys, $sqlstring);

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{source} = $sqlstring;
	$self->{id} = $id;
	bless $self, $class;	
	if(scalar(@$retrieved_array) && scalar @{ $retrieved_array->[0] } == $numkeys) ##Note: scalar(@rows) necessary to allow for empty tables
	{
		push @table_header, "_xcount";
	}
	
	$self->add_columnnames(@table_header);	
	return $self;
}
sub create_from_array {
	my ($class, $array,  $numkeys,$id, $header) =  @_; 
	croak "Error: Create_from_array is a class method, you have called it from an object!\n" if (ref $class) and return undef;

	## conditional to load query result into $retrieved_array
							
	my $hash = create_index_structure($array, $numkeys, "Array");

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	bless $self, $class;	
	if (defined $header)
	{	if(scalar(@$array) && scalar @{ $array->[0] } == $numkeys)
		{
			push @$header, "_xcount";
		}
		$self->add_columnnames(@$header) ;	
	}
	return $self;
}
sub create_index_structure {
	my ($retrieved_array , $numkeys , $sqlstring) = @_;
	my $hash = {};
	for my $j (@{$retrieved_array}){
		my $rt = $hash;
		if (@{$j} < $numkeys) {
			warn "\"$sqlstring\"  results in a table lacking necessary columns.\n" and return undef;
		}
		my $lastcolumn = $#{$j};
		my (@keylist, @columns);
		$numkeys > 1 ? @keylist = @{$j}[0..$numkeys-1] : $keylist[0] = ${$j}[0];
		if ($numkeys < $lastcolumn) {
			@columns = @{$j}[$numkeys..$lastcolumn]
		}
		elsif ($numkeys == $lastcolumn) {
			$columns[0] = ${$j}[$numkeys];
		}
		else {
		      $columns[0] = 1;
		}

		my $lastkey = pop @keylist;
		foreach my $key (@keylist) {
			if (not defined $key) {
				$key = "NA";
				print join(":",@keylist) ." Undefined key set to NA! \n$sqlstring\n";
			};
			if (not exists($rt->{$key})) {
				$rt->{$key} = {};
			}

			$rt = $rt->{$key};
		}
		if (not defined $lastkey) {
			$lastkey = "NA"; 
			print join(":",@keylist) ." Undefined key set to NA! \n$sqlstring\n";
		};	
  		$rt->{$lastkey} = \@columns;	
	}
	return $hash;
}
sub create_from_ascii {
	my ($class, $file, $numkeys, $id, $separator, $header) = @_;
	croak "Error: Create_from_ascii is a class method, you have called it from an object!\n" if (ref $class) and return undef;
	$separator = '\t' unless (defined $separator);
	$header = 1 unless (defined $header);
	
	#print "Reading data from $file...\n";
	open (my $infile, "<", $file) or (croak "Error when opening $file: $! \n\n" and return undef);
	my $i = 0;
	my @table_header;
	my @rows;
	while (<$infile>) {
		chomp;
		my $Line =  $_;
		next if ($Line =~ /^\s*$/); #skip empty lines
		$i++;

		if ($i == 1 and $header) {
			@table_header = split /$separator/, $Line;
		}
		else {
			my @currow = split /$separator/, $Line, -1; #-1 avoids trailing separators to be cut off, so that the number of fields should always match even with empty fields
			#print join (", ", @currow),"\n";
			@currow = map { length($_) ? $_ : undef } @currow;
			push @rows, \@currow;
		}
	}
	close ($infile);
	my $hash = create_index_structure(\@rows, $numkeys, $file);

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	
	bless $self, $class;
	if ($header){
		if(scalar(@rows) && scalar @{ $rows[0] } == $numkeys) ##Note: scalar(@rows) necessary to allow for empty tables
		{
			push @table_header, "_xcount";
		}
		$self->add_columnnames(@table_header);
	}
	return $self;
}
sub create_from_hash {
	my ($class, $hash, $numkeys, $id, $table_header) = @_;
	croak "Error: Create_from_hash is a class method, you have called it from an object!\n" if (ref $class) and return undef;

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	bless $self, $class;
	if (defined $table_header) {
		$self->add_columnnames(@$table_header);
	}
	return $self;
}
sub create_from_string {
	my ($class, $instring, $sep, $linesep, $numkeys, $id, $header) = @_;
	croak "Error: Create_from_string is a class method, you have called it from an object!\n" if (ref $class) and return undef;
	$sep = '\t' unless (defined $sep);
	$linesep = '\n' unless (defined $linesep);
	$header = 1 unless (defined $header);
	$instring =~ s/^\s+//;
	
	my @lines = split /$linesep/, $instring;
	my $i = 0;
	my @table_header;
	my @rows =();
	foreach my $Line (@lines) {
		
		next if ($Line =~ /^\s*$/); #skip empty lines
		$i++;

		if ($i == 1 and $header) {
			@table_header = split /$sep/, $Line;
		}
		else {
			my @currow = split /$sep/, $Line;
			push @rows, \@currow;
		}
	}
	
	my $hash = create_index_structure(\@rows, $numkeys, $id);

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	
	bless $self, $class;
	if ($header){
		if(scalar(@rows) && scalar( @{ $rows[0] }) == $numkeys) ##Note: scalar(@rows) necessary to allow for empty tables
		{
			push @table_header, "_xcount";
		}
		$self->add_columnnames(@table_header);
	}
	return $self;
}
sub create_from_SPARQL {
	my ($class, $endpoint, $sparqlstring, $numkeys, $id) =  @_; 
	# $endpoint:	html endpoint
	# $sparqlstring:	an sql string resulting in a table
	# $numkeys:	the number of primary key columns (have to be the first columns to be returned from the database)
	# $id:		ID with which the table can be referred to in registries
	
	require RDF::Query::Client;

	## conditional to load query result into $retrieved_array
	my @retrieved_array; 
	my @table_header;
	eval{	#exception handling to avoid fatals because table column does not exist 
								
		local $SIG{__WARN__}=sub{}; #Temporarily suppress warnings
		
		my $ua = LWP::UserAgent->new(timeout => 100);
		my $query = RDF::Query::Client->new($sparqlstring); 
		my $iterator = $query->execute($endpoint, {UserAgent => $ua});

		croak "SPARQL query returned empty result for SPARQL: $sparqlstring\n\n".
					$query->http_response()->code()." ". $query->error()."\n".$query->http_response->message()."\n"
						unless (defined $iterator->{_names}) 

						;

		@table_header = @{$iterator->{_names}};		
		
		while (my $row = $iterator->next()) {
		  my @formatted_row;  
		  for my $col (@{$iterator->{_names} }) {
			  if (!defined($row->{$col}) ) {
				push @formatted_row, undef;
			  }
			  elsif (ref($row->{$col}) eq 'RDF::Trine::Node::Literal') {
				push @formatted_row,   $row->{$col}[0];
			  }
			  else {
				push @formatted_row, $row->{$col}->as_string();
			  }
		  } 
		  push @retrieved_array, \@formatted_row; 
		}
	};
	if ($@) {
		croak "$@\n For SPARQL: $sparqlstring\n";
	}
							
	my $hash = create_index_structure(\@retrieved_array, $numkeys, $sparqlstring);

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{source} = $sparqlstring;
	$self->{id} = $id;
	bless $self, $class;	
	if(scalar(@retrieved_array) && scalar @{$retrieved_array[0]} ==  $numkeys) ##Note: scalar(@rows) necessary to allow for empty tables
	{
		push @table_header, "_xcount";
	}
	$self->add_columnnames(@table_header);	
	return $self;
}
sub create_empty {
	my $class = shift;
	my $numkeys = shift;
	my $id = shift;
	my $header = shift;
	my @table_header;
	if (defined $header) {
		@table_header = @$header;
	}

	my $hash = {};

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	
	bless $self, $class;
	if ($header){
		$self->add_columnnames(@table_header);
	}
	return $self;

}
sub regrouped_copy_by_colnames {
    my ($self,$newkeys,$id, $newnonkeys) = @_;
	my @newkeycolids = map { $self->column_by_name_keys_allowed($_) ; } @$newkeys;

	my @newnonkeycolids;
	@newnonkeycolids = map { $self->column_by_name_keys_allowed($_) ; } @$newnonkeys if (defined $newnonkeys and scalar @$newnonkeys);

	return $self->regrouped_copy(\@newkeycolids, $id, \@newnonkeycolids);
}
sub regrouped_copy {
    my ($self,$newkeysarray,$id, $newnonkeycolids) = @_;
#    croak "Error: IndexedTable ".$self->id." is empty\n" if ($self->is_empty);


    my $numkeys = scalar @$newkeysarray;    
    my @newarray;
    my @colstofront = map {$_ - 1} (@$newkeysarray);
    my @colsatend = ();
    my $arrayref = $self->as_array_ref;
    if (defined ($newnonkeycolids) and scalar (@$newnonkeycolids) ) {
	    @colsatend = map {$_ - 1} @$newnonkeycolids;
    }
    else {
        if ($self->{header}) {
            @colsatend = (0..$#{$self->{header}}  );
        } 
        elsif (not $self->is_empty ) {
	        @colsatend =  (0..$#{$arrayref->[0]});
	    }
	    my $cc = 0;
	    foreach  (sort {$a <=> $b} @colstofront) {
		    splice @colsatend, $_ - ($cc++), 1;
	    }
    }
    if (not $self->is_empty ) {

        @newarray = map {[( @{$_}[@colstofront],  @{$_}[@colsatend]  ) ]  } @$arrayref;
    }
    my $hash = create_index_structure(\@newarray, $numkeys, "regrouping ". $self->{id});
    my $new = {};
    $new->{numkeys} = $numkeys;
    $new->{table} = $hash;
    $new->{id} = $id;
    bless $new, ref $self;
    
    if ($self->{header}){
		my @new_table_header = ( @{$self->{header}}[@colstofront],@{$self->{header}}[@colsatend]  ) ;
		
		#print "HERE: ",join(":",@new_table_header),"\n";
		$new->add_columnnames(@new_table_header);
	}
    

    return $new;
}

sub add_rows {
	my ($self, $rows) = @_;
	my $hash_to_add = create_index_structure($rows, $self->numkeys, "");
	if (defined $hash_to_add) {
		eval {	#exception handling
								
			local $SIG{__WARN__}=sub{}; #Temporarily suppress warnings
			my $merge = Hash::Merge->new('LEFT_PRECEDENT');
			my $temp  =   $merge->merge ( $hash_to_add, $self->{table} );
			$self->{table} = $temp;
		};
		if ($@) {
			croak "$@"."\n\n";
		}
	}

}
sub add_row {
	my ($self, $rows) = @_;
	my @rowarray = ($rows);
	eval {	#exception handling to avoid fatals because table column does not exist 						
		local $SIG{__WARN__}=sub{}; #Temporarily suppress warnings
		$self->add_rows(\@rowarray);
	};
	if ($@) {
		croak "$@"."\n\n";
	}
}
sub merge_with {
	my ($self, $secondTable) = @_;
	
	croak ("Error in IndexedTable->merge_with : Second table not a valid IndexedTable, ref: ".ref($secondTable)."\n") if (ref($secondTable) ne 'IndexedTable' and ref($secondTable) ne 'IndexedTableLink');
    croak ("Error: Trying to merge incompatible tables: Not the same number of keys \n") if ($secondTable->numkeys != $self->numkeys())	;
	
	
	if (keys  %{$secondTable->{table}} ) {
		eval {	#exception handling
								
			local $SIG{__WARN__}=sub{}; #Temporarily suppress warnings
			my $merge = Hash::Merge->new('LEFT_PRECEDENT');
			my $temp  =   $merge->merge ( $secondTable->{table}, $self->{table} );
			$self->{table} = $temp;
		};
		if ($@) {
			croak "$@"."\n\n";
		}
	}
	return $self;
}


sub query {
	my ($self , $keysref, $column, $sort) = @_;
	$sort = 0 unless (defined $sort);
	
	my @keylist = @{$keysref};
	#if (@keylist == 0) {
	#	die "You did not specify a key when requesting table entry\n" ;
	#}
	if (@keylist > $self->{numkeys}) {
		croak "Table entry referred to by more keys than defined for table\n" ;
	}
	#my $lastkey = pop @keylist;
 	my $rt = $self->{table};
	
	foreach my $key (@keylist) {
		croak "Undefined key in keylist of query to IndexedTable!\n" unless (defined $key);
		 #print "no value found for $key in table.\n" and 
		if (not exists($rt->{$key})) {
			if (@keylist < $self->{numkeys}) {
				return ();
			}
			else {
				return undef;
			}
		}	
		$rt = $rt->{$key};
		
	}

	if (@keylist == $self->{numkeys} and $column >= 0) {
		#(croak "Column $column is a key field in table $self->{id}\n" and return undef) if ($column - $self->{numkeys} -1 < 0) ;
		
		(carp("Warning in table reference: $column is a key column in table $self->{id}\nThis will simply return the key component that you have specified as a scalar value\n") 
				and return $keylist[$column -1] ) if ($column - $self->{numkeys} -1 < 0) ;
		
		if (ref $rt eq "ARRAY") {
			(croak "Column $column does not exist in table $self->{id}\n"  and  return undef) if ($column - $self->{numkeys} -1 > $#{ $rt }) ;
			return $rt->[$column -$self->{numkeys} - 1];
		}
		elsif (not ref $rt) {
			return $rt;
		}
	}
	elsif 	(@keylist == $self->{numkeys} and $column == -1) {
		return @$rt;
	}
	elsif (@keylist < $self->{numkeys}) {
		if ($sort == 1) {
				return sort {$a cmp $b } keys(%{ $rt });
		}
		elsif ($sort == 2) {
				return sort {$a <=> $b } keys(%{ $rt });
		}
		return keys(%{ $rt });
	}
}
sub query_by_columnname {
	my ($self , $keysref, $columnname, $sort) = @_;
	my $column = $self->column_by_name($columnname);
	return $self->query($keysref,$column, $sort);
}

sub query_by_columnname2 {
	my ($self , $keysref, $columnname, $sort) = @_;
	my $column = $self->column_by_name($columnname);
	
	if ( scalar @{$keysref} ==  $self->{numkeys}) {
		if ($self->{numkeys} == 1) {
			return $self->{table}->{$keysref->[0]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 2) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 3) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->{$keysref->[2]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 4) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->{$keysref->[2]}->{$keysref->[3]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 5) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->{$keysref->[2]}->{$keysref->[3]}->{$keysref->[4]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 6) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->{$keysref->[2]}->{$keysref->[3]}->{$keysref->[4]}->{$keysref->[5]}->[$column -$self->{numkeys} - 1];
		}
		elsif ($self->{numkeys} == 7) {
			return $self->{table}->{$keysref->[0]}->{$keysref->[1]}->{$keysref->[2]}->{$keysref->[3]}->{$keysref->[4]}->{$keysref->[5]}->{$keysref->[6]}->[$column -$self->{numkeys} - 1];
		}
	}
	
	return $self->query($keysref,$column, $sort);
}





sub column_by_name {
	my ($self ,  $columnname) = @_;
	(print STDERR "Error: Columns in IndexedTable $self->{id} are not named\n" and exit(1)) unless (exists $self->{columns});
	(print STDERR "Error: Column $columnname does not exist in IndexedTable $self->{id}\nColumns:".join(", ", sort ({ $self->{columns}->{$a} <=> $self->{columns}->{$b} } keys( %{$self->{columns}}) ) )."\n" and exit(1)) unless (exists $self->{columns}->{$columnname});
	(print STDERR "Error: Column $columnname is a key column in IndexedTable $self->{id}\n" and exit(1)) unless ( $self->{columns}->{$columnname}  > $self->{numkeys});
	
	my $column = $self->{columns}->{$columnname};
	return $column;
}
sub column_by_name_keys_allowed {
	my ($self ,  $columnname) = @_;
	(print STDERR "Error: Columns in IndexedTable $self->{id} are not named\n" and exit(1)) unless (exists $self->{columns});
	(print STDERR "Error: Column $columnname does not exist in IndexedTable $self->{id}\nColumns:".join(", ", sort ({ $self->{columns}->{$a} <=> $self->{columns}->{$b} } keys %{$self->{columns}}) )."\n" and exit(1)) unless (exists $self->{columns}->{$columnname});
	
	my $column = $self->{columns}->{$columnname};
	return $column;
}
sub exists_columnname {
	my $self = shift;
	my $columnname = shift;
	return (exists $self->{columns}->{$columnname});
}
sub get_name_of_column {
	my ($self ,  $column) = @_;
	return $self->{header}[$column -1 ];
}
sub is_empty {
  return 1 if (scalar (keys %{$_[0]->{table}} )== 0);
  return 0;

}

sub add_columnnames {
	my $self = shift;
	my @columnnames = @_;
	$self->{header} = \@columnnames;	
	for my $i (0.. $#columnnames) {
		my $cname = $columnnames[$i];
		print "Warning: Incomplete header for table ".$self->{id}."\n" unless (defined $cname);
		$self->{columns}->{$cname} = $i + 1;	
	}
	return 1;
}

sub printtable {
	my $self = shift;
	print Dumper($self->{table});
	
}
sub as_array_ref {
      my $self = shift;
      my @array;
      my @curKeyArray;
      my @PointerAtLevel;
      my $numkeys = $self->numkeys;
	$#PointerAtLevel = $numkeys + 1;
	@PointerAtLevel = (0) x @PointerAtLevel; 
        my $level = 1;
	PRINTOUTER: while ($level > -1) {
		PRINTINNER: while ($level <= $numkeys) {
			my @keys = $self->query(\@curKeyArray,0);

			if ($PointerAtLevel[$level] < @keys) {
				push @curKeyArray, $keys[$PointerAtLevel[$level]];
				$PointerAtLevel[$level] += 1;
				$level++;
			}
			elsif  ($level == 1 and  $PointerAtLevel[$level] >= @keys ) {
				$level = -1;
				last PRINTOUTER;
			}
			else {
				$PointerAtLevel[$level] = 0;
				$level--;
				pop @curKeyArray;
			}			

		}
		my @cols = $self->query(\@curKeyArray,-1);
		push @array, [(@curKeyArray, @cols )];
		pop @curKeyArray;
		$level--;
	}
      return \@array;
}
sub rows_as_array_ref {
      my ($self ,$baseKeyArray) = @_;
      my @array;
      my @curKeyArray =@$baseKeyArray;
      my @PointerAtLevel;
      my $numkeys = $self->numkeys - scalar(@$baseKeyArray);
	$#PointerAtLevel = $numkeys + 1;
	@PointerAtLevel = (0) x @PointerAtLevel; 
        my $level = 1;
	PRINTOUTER: while ($level > -1) {
		PRINTINNER: while ($level <= $numkeys) {
			my @keys = $self->query(\@curKeyArray,0);

			if ($PointerAtLevel[$level] < @keys) {
				push @curKeyArray, $keys[$PointerAtLevel[$level]];
				$PointerAtLevel[$level] += 1;
				$level++;
			}
			elsif  ($level == 1 and  $PointerAtLevel[$level] >= @keys ) {
				$level = -1;
				last PRINTOUTER;
			}
			else {
				$PointerAtLevel[$level] = 0;
				$level--;
				pop @curKeyArray;
			}			

		}
		my @cols = $self->query(\@curKeyArray,-1);
		push @array, [(@curKeyArray, @cols )];
		pop @curKeyArray;
		$level--;
	}
      return \@array;
}
sub subtable_as_array_ref {
      my ($self ,$baseKeyArray, $columnList) = @_;
      
      ##transform column names to column indices
      for my $c (0..$#{$columnList}) {
		next if (not defined $columnList->[$c] or $columnList->[$c] =~ /^\s*\d+\s*$/);
		$columnList->[$c] = $self->column_by_name($columnList->[$c]);
	  }
	  my $temp = $self->rows_as_array_ref($baseKeyArray);
	  my @array;
	  for my $r (0..$#{$temp}) {
		  for my $c (0..$#{$columnList}) {
			push @{$array[$r]}, $temp->[$r][$columnList->[$c] -1]; 
		  }
	  }
	  return \@array;
}
sub printme {
	my $self = shift;
	my $file = shift;
	my $splitter = shift || "\t";
	my $out;
	if (defined $file) {
		open ($out, ">", $file ) or croak "Error: Could not open $file: $!\n";
	}
	else {
		open ($out, ">&STDOUT" );
	}
	
	map { print $out join($splitter, @{$_})."\n" } @{$self->as_array_ref};

	close($out);
}
sub printme_with_header {
	my $self = shift;
	my $file = shift;
	my $splitter = shift || "\t";
	my $null = shift || "";
	my $out;
	if (defined $file) {
		open ($out, ">", $file ) or croak "Error: Could not open $file: $!\n";
	}
	else {
		open ($out, ">&STDOUT" );
	}
	print $out join($splitter, @{$self->{header}})."\n" ;
	map { print $out join($splitter, map{ !defined ($_) ? $null : $_  } @{$_})."\n" } @{$self->as_array_ref};

	close($out);
}
sub printme_with_header_sorted {
	my $self = shift;
	my $file = shift;
	my $splitter = shift || "\t";
	my $out;
	if (defined $file) {
		open ($out, ">", $file ) or croak "Error: Could not open $file: $!\n";
	}
	else {
		open ($out, ">&STDOUT" );
	}
	
	$self->printme_with_header_sorted_to_fh($out, $splitter);
	close($out);
}
sub printme_with_header_sorted_to_fh {
	my $self = shift;
	my $out = shift;
	my $splitter = shift || "\t";
	print $out join($splitter, @{$self->{header}})."\n" ;
	eval { use warnings FATAL => qw/uninitialized/;
		map { print $out join($splitter, @{$_})."\n" } sort{ $self->row_sorter($a, $b)	} @{$self->as_array_ref};
	};
	if ($@) {
		print STDERR "Unitialized value when printing table '".$self->{id}."':\n";
		exit(1);
	}	
}
sub row_sorter {
	my $i = 0;
	while ($i < $_[0]->numkeys() )
	{	my $r = $_[1]->[$i] cmp $_[2]->[$i];
		return $r if ($r);
		++$i;
	}
	return 0;
}

sub print_to_fh {
	my $self = shift;
	my $out = shift;
	my $splitter = shift || "\t";
	eval { use warnings FATAL => qw/uninitialized/;
		map { print $out join($splitter, @{$_})."\n" } @{$self->as_array_ref};
	};
	if ($@) {
		print STDERR "Unitialized value when printing table '".$self->{id}."':\n";
		exit(1);
	}
}
sub print_sorted_to_fh {
	my $self = shift;
	my $out = shift;
	my $splitter = shift || "\t";
	eval { use warnings FATAL => qw/uninitialized/;
		map { print $out join($splitter, @{$_})."\n" } sort{ $self->row_sorter($a, $b)	} @{$self->as_array_ref};
	};
	if ($@) {
		print STDERR "Unitialized value when printing table '".$self->{id}."':\n";
		exit(1);
	}
}
sub set_column_value {
	my ($self, $keysref, $column, $value) = @_;
	my @keylist = @{$keysref};
	#if (@keylist == 0) {
	#	die "You did not specify a key when requesting table entry\n" ;
	#}
	if (@keylist > $self->{numkeys}) {
		croak "Table entry referred to by more keys than defined for table\n" ;
	}
	#my $lastkey = pop @keylist;
 	my $rt = $self->{table};
	my $lastkey = pop @keylist;
	foreach my $key (@keylist) {
		 #print "no value found for $key in table.\n" and 
		#croak "No entry found for $key in table.\n" if (not exists($rt->{$key}));
		if (not exists($rt->{$key})) {
			$rt->{$key} = {};
		}
		$rt = $rt->{$key};
	}

	if (@keylist+1 == $self->{numkeys}) {
		(warn "Column $column is a key field in table $self->{id}\n" and return undef) if ($column - $self->{numkeys} -1 < 0) ;
		$rt->{$lastkey} = [] unless (exists $rt->{$lastkey});
		$rt->{$lastkey}->[$column -$self->{numkeys} - 1] = $value;
		
	}
	elsif (@keylist +1 < $self->{numkeys}) {
		croak "Not enough keys supplied\n";
	}
}
sub numkeys {
    return $_[0]->{numkeys};
}


##compatibility interface function that allows to call transform on an already created IndexedTable as if it was an MpmasQLTableDefinition
## will just do a consistency check and return itself
sub transform  {
	my ($self, $dbh, $numkeys) = @_;
	
	croak ("Error: Incompatible call to IndexedTable -> transform \n")	if ($numkeys != $self->numkeys());
	return $self;
}


sub create_from_spreadsheet {
	my ($class, $file,$sheetname, $numkeys, $id, $header) = @_;
	croak "Error: create_from_spreadsheet is a class method, you have called it from an object!\n" if (ref $class) and return undef;
	$header = 1 unless (defined $header);
	
	require Spreadsheet::Read;
	
	
	#print "Reading data from $file...\n";
	
	my $workbook;
	if (exists $cacheWorkbooks{$file}) {
		$workbook = $cacheWorkbooks{$file};
	}
	else {
      		$workbook =  Spreadsheet::Read->new ($file, clip => 1, strip => 3);
		croak "Error when opening $file\n\n" and return undef unless (defined $workbook);;
		croak "Error when opening $file: No sheets.\n\n" and return undef unless ($workbook->sheets());;
		$cacheWorkbooks{$file} = $workbook if ($useCacheWorkbooks > 0);
	}	
	my $worksheet = $workbook->sheet($sheetname);
	croak "Error when opening $file: Sheet '$sheetname' does not exist. Number of sheets: ".$workbook->sheets()."\n\n" and return undef unless (defined $worksheet);

	
	my $i = 0;
	my @temp = $worksheet->rows();
	my @table_header = @{$temp[0]};
	my @rows = @temp[1.. $#temp ];

	my $hash = create_index_structure(\@rows, $numkeys, $file."/".$sheetname);

	my $self = {};
	$self->{numkeys} = $numkeys;
	$self->{table} = $hash;
	$self->{id} = $id;
	
	bless $self, $class;
	if ($header){
		if(scalar(@rows) && scalar @{ $rows[0] } == $numkeys) ##Note: scalar(@rows) necessary to allow for empty tables
		{
			push @table_header, "_xcount";
		}
		$self->add_columnnames(@table_header);
	}
	
	if ($useCacheWorkbooks == 1 && defined $lastworkbookfile && $lastworkbookfile ne $file ) {
		delete $cacheWorkbooks{$lastworkbookfile};
		$lastworkbookfile = $file;
	}
	elsif ($useCacheWorkbooks == 1 && not defined $lastworkbookfile) {
		$lastworkbookfile = $file;
	}
	
	
	return $self;
	
}	
sub set_id {
		my ($self, $id) = @_;
		$self->{id} = $id;

}
sub distinct_values_for_columnname {
	my ($self, $indexArray, $columnname) = @_;
	my $col = $self->column_by_name_keys_allowed($columnname);
	
	
	if ($col <= $self->numkeys() &&  scalar(@$indexArray) == $col -1  ) {
		#Column is a key column and key array is complete up until key
		#take simple way using query
		return $self->query($indexArray, 0);
	}
	elsif ($col > $self->numkeys() &&  scalar(@$indexArray) ==  $self->numkeys()  ) {
		#Desired column not key column and complete specification [return single value as set/array]
		return ($self->query($indexArray, $col));
	}
	elsif ($col <= $self->numkeys() &&  scalar(@$indexArray) < $col -1  ) {
		return $self->distinct_values_for_column($indexArray, $col);

	}
	elsif ($col > $self->numkeys() &&  scalar(@$indexArray) <  $self->numkeys()  ) {
		return $self->distinct_values_for_column($indexArray, $col);
	}
	elsif (scalar(@$indexArray) > $self->numkeys()) {
		croak ("Error in #distinct_values_for_column() or table set reference for ".$columnname."@".$self->{id}."[". join(",", @$indexArray). "]: Index has too many elements!\n");		
	}
	else {
		croak ("Error in #distinct_values_for_column() or table set reference for ".$columnname."@".$self->{id}."[". join(",", @$indexArray). "]: Unforeseen case");		
	}

}
sub distinct_values_for_column {
	my ($self, $indexArray, $column, $sort) = @_;
	$sort = 0 unless (defined $sort);

	my @keylist = @{$indexArray};


	#Check cache whether query had been used before:
	#if ($useCacheDistinct && exists $self->{cache}->{distinct}->{$column}) {
	#	
	#	
	#}

	if (@keylist > $self->{numkeys}) {
		croak "Table entry referred to by more keys than defined for table\n" ;
	}

	my @result;
	
	#Traverse through key array to find proper subentry
 	my $rt = $self->{table};
	foreach my $key (@keylist) {
		croak "Undefined key in keylist of query to IndexedTable!\n" unless (defined $key);
		if (not exists($rt->{$key})) {
			return ();
		}	
		$rt = $rt->{$key};
	}
	if (@keylist <= $self->{numkeys}) {
		if ($column  <= $self->{numkeys} ) {
			#Key column
			if($column  == scalar(@keylist) +1) {
				return keys(%{ $rt }); ##should not have got here when used #distinct_values_for_columnname, diverted to query(,0)
			}
			elsif ($column <= scalar(@keylist)) {
				(carp "Warning in  distinct_values_for_column: $column is a key column in table $self->{id}\n
				The specified index includes this $column and will simply return the  key component that you have specified as a set\n" 
					and return ($keylist[$column-1] )) # nothing to sort here
				if ($column - $self->{numkeys} -1 < 0) ;
			}
			else {
				my @curKeyArray = @keylist;
				my @PointerAtLevel;
				my $numkeys = $column - scalar(@keylist)-1;
				$#PointerAtLevel = $numkeys + 1;
				@PointerAtLevel = (0) x @PointerAtLevel; 
				my $level = 1;
				my %collect;
				PRINTOUTER: while ($level > -1) {
					PRINTINNER: while ($level <= $numkeys) {
						my @keys = $self->query(\@curKeyArray,0);

						if ($PointerAtLevel[$level] < @keys) {
							push @curKeyArray, $keys[$PointerAtLevel[$level]];
							$PointerAtLevel[$level] += 1;
							$level++;
						}
						elsif  ($level == 1 and  $PointerAtLevel[$level] >= @keys ) {
							$level = -1;
							last PRINTOUTER;
						}
						else {
							$PointerAtLevel[$level] = 0;
							$level--;
							pop @curKeyArray;
						}			

					}
					#my $value = $self->query(@curKeyArray,0);
					for my $k ( $self->query(\@curKeyArray,0)) {
						$collect{$k} = 1;
					}
					pop @curKeyArray;
					$level--;
				}
				@result = keys %collect;
			}
			
		}
		elsif ((ref ($rt) eq "ARRAY") and ($column - $self->{numkeys} -1 > $#{ $rt } )
				or (not ref ($rt) and $column - $self->{numkeys} -1 > 0)
				)  {
			(croak "Column $column does not exist in table $self->{id}\n"  and return undef) 

		}
		else { #Not a key column
				my @curKeyArray =@keylist;
				my @PointerAtLevel;
				my $numkeys = $self->numkeys - scalar(@keylist);
				$#PointerAtLevel = $numkeys + 1;
				@PointerAtLevel = (0) x @PointerAtLevel; 
				my $level = 1;
				my %collect;
				PRINTOUTER: while ($level > -1) {
					PRINTINNER: while ($level <= $numkeys) {
						my @keys = $self->query(\@curKeyArray,0);

						if ($PointerAtLevel[$level] < @keys) {
							push @curKeyArray, $keys[$PointerAtLevel[$level]];
							$PointerAtLevel[$level] += 1;
							$level++;
						}
						elsif  ($level == 1 and  $PointerAtLevel[$level] >= @keys ) {
							$level = -1;
							last PRINTOUTER;
						}
						else {
							$PointerAtLevel[$level] = 0;
							$level--;
							pop @curKeyArray;
						}			

					}
					my $value = $self->query(\@curKeyArray,$column);
					
					$collect{$value} = 1;
					pop @curKeyArray;
					$level--;
				}
				@result = keys %collect;
		}
			
	}
	elsif (@keylist == $self->{numkeys}) {
		##should not have got here even when used #distinct, diverted to query(,x)
		(carp "Warning in  distinct_values_for_column: $column is a key column in table $self->{id}\n
				The specified index includes this $column and  will simply return the  key component that you have specified as a set\n" 
					and return ($keylist[$column-1]) ) # nothing to sort here
				if ($column - $self->{numkeys} -1 < 0) ;
		
		if (ref $rt eq "ARRAY") {
			(croak "Column $column does not exist in table $self->{id}\n"  and  return undef) 
				if ($column - $self->{numkeys} -1 > $#{ $rt }) ;
			return ($rt->[$column -$self->{numkeys} - 1]); # nothing to sort here
		}
		elsif (not ref $rt) {
			return ($rt) # nothing to sort here;
		}
	}

	if ($sort == 1) {
		return sort {$a cmp $b } @result;
	}
	elsif ($sort == 2) {
		return sort {$a <=> $b } @result;
	}
	else {
		return @result;
	}
}
sub add_columns {
	my ($self, $columnnames, $columnvalues)= @_;
	
     # my @array;
        my @curKeyArray;
      	my @PointerAtLevel;
      	my $numkeys = $self->numkeys;
	$#PointerAtLevel = $numkeys + 1;
	@PointerAtLevel = (0) x @PointerAtLevel; 
        my $level = 1;

	PRINTOUTER: while ($level > -1) {
		PRINTINNER: while ($level <= $numkeys) {
			my @keys = $self->query(\@curKeyArray,0);

			if ($PointerAtLevel[$level] < @keys) {
				push @curKeyArray, $keys[$PointerAtLevel[$level]];
				$PointerAtLevel[$level] += 1;
				$level++;
			}
			elsif  ($level == 1 and  $PointerAtLevel[$level] >= @keys ) {
				$level = -1;
				last PRINTOUTER;
			}
			else {
				$PointerAtLevel[$level] = 0;
				$level--;
				pop @curKeyArray;
			}			

		}
	        my $rt = $self->{table};
		foreach my $key (@curKeyArray) {
			croak "Undefined key in keylist of query to IndexedTable!\n" unless (defined $key);
			$rt = $rt->{$key};
		}	

		push @$rt, @$columnvalues;
		pop @curKeyArray;
		$level--;
	}
	push @{ $self->{header}}, @$columnnames;	
	for my $i (0.. $#{ $self->{header}} ) {
		my $cname = $self->{header}[$i];
		print "Warning: Incomplete header for table ".$self->{id}."\n" unless (defined $cname);
		$self->{columns}->{$cname} = $i + 1;	
	}
	
}
sub is_as_expected {
	my ($self, $expected) = @_;

	return 0 if (exists $expected->{numKeys} and  $expected->{numKeys} != $self->numkeys);
	return 0 if (exists $expected->{numCols} and  $expected->{numCols} != scalar(@{ $self->{header}}));
	if (exists $expected->{header}) {
		for my $i (0..$#{$expected->{header}} ) {
			return 0 if (   defined $expected->{header}[$i] && $expected->{header}[$i] ne $self->{header}[$i]  );
		}
	}	
	return 1;	
}
1;
