#######################################################################
# This software has been created by Genome Research Limited (GRL).    # 
# GRL hereby grants permission to use, copy, modify and distribute    # 
# this software and its documentation for non-commercial purposes     # 
# without fee at the user's own risk on the basis set out below.      #
# GRL neither undertakes nor accepts any duty whether contractual or  # 
# otherwise in connection with the software, its use or the use of    # 
# any derivative, and makes no representations or warranties, express #
# or implied, concerning the software, its suitability, fitness for   #
# a particular purpose or non-infrin gement.                          #
# In no event shall the authors of the software or GRL be responsible # 
# or liable for any loss or damage whatsoever arising in any way      # 
# directly or indirectly out of the use of this software or its       # 
# derivatives, even if advised of the possibility of such damage.     #
# Our software can be freely distributed under the conditions set out # 
# above, and must contain this copyright notice.                      #
#######################################################################

##################
# Examples of use
##################
# my $db = 'platetrack';
# my $STATEMENTS = statements->new("$db");       # Make a new db connection object using a WrapDBI user id
#
# $STATEMENTS->{debug} = 1                       # Optionally turn debugging on
#
# $STATEMENTS->default_sql_lib_path              # View the existing default path to the sql libraries
# $STATEMENTS->default_sql_lib_path('./');       # Change the default path to the sql libraries
#
# $STATEMENTS->available_sql_libs("show_sql");   # View the available sql (libraries)
# $STATEMENTS->available_sql_libs("");           #                        (libraries and contents)
#
# $STATEMENTS->load_sql_lib(["test1.sql_lib::all", "test2.sql_lib::all"]);
                                                 # Load and prepare all SQL from 2 libraries
# $STATEMENTS->load_sql_lib(["test1.sql_lib::all", "test2.sql_lib::some_sql1", "test2.sql_lib::some_sql2"];
                                                 # Load and prepare all SQL from 1 library and selective
                                                 # statements from another library

#
# Format for sql_lib entries
###################
## Comment 1
###################
#
## Comment 2
#>all_available_categories<
#select scd.description, scd.id_dict
#from seqcategorydict scd
#
# [Leave at least 2 blank lines between piece of SQL]
#
## Comment 3
#>available_categories<
#select distinct ps.category, scd.description, scd.id_dict
#from procstrategy ps,
#seqcategorydict scd
#where ps.category = scd.id_dict
#and teamname = ?
#and user_selectable = 1
#
#
# my ($this) = $STATEMENTS->get('test1.sql_lib::some_sql');  # Execute a select with no binds from a library 
# my ($this) = $STATEMENTS->get('test1.sql_lib::some_sql2', ['a', 'b', 'c']); # Execute a select with binds
#
# my ($this) = $STATEMENTS->insert('test1.sql_lib::some_sql3', ['a', 'b', 'c']); # Execute an insert with binds
#
# $STATEMENTS->end; # Clean up and disconnect

package statements;
use WrapDBI;
use Carp;
use Data::Dumper;

sub new {
    my ($class, $db) = @_;
    croak "SQL_ENGINE_ERROR No database user id supplied !" unless (defined $db);
    my $self = {};
    $self->{wrap_user_id} = $db;
    $self->{default_sql_lib_path} = "/usr/local/badger/bin/";
    $self->{debug} = 0;
    bless $self, $class;
    $self->connect;
    return $self;
}


sub connect {
    my $self = shift;
    eval {
	$self->{DBH} = WrapDBI->connect("$self->{wrap_user_id}", {RaiseError => 1});
    };
    if ($@) {
	croak "SQL_ENGINE_ERROR Problems connecting as $self->{wrap_user_id}; Check that the database is up and that you are connecting with the correct user !\n$@";
    }
    $self->{DBH}->{autocommit} = 0;
    # Which instance are we connected to ? This is controlled via DBlogs
}


sub default_sql_lib_path {
    my $self = shift;
    if (@_) { 
	my $prev = $self->{default_sql_lib_path};
	$self->{default_sql_lib_path} = shift;
	if ($self->{debug} == 1){
	    carp "Old SQL library path $prev";
	}
    }
    if ($self->{debug} == 1){
	carp "Current SQL library path is $self->{default_sql_lib_path}";
    }
    return $self->{default_sql_lib_path};
}

#### New test
sub search {
    
    my ($self, $string) = @_;

    if ($string eq ''){
        croak "SQL_ENGINE_ERROR You must enter a search string !\n";
    }
    
    if ($string =~ /[a-zA-Z_]+$/){
        print "Searching for $string ....  \n\n";
    }
  #  else{ croak "Your search string must only contain letters, numbers and underscores\n";
   # }

    # Look in $self->{default_sql_lib_path} for *.sql_lib files
    opendir(DIRHANDLE, "$self->{'default_sql_lib_path'}") || croak "SQL_ENGINE_ERROR Cannot open default SQL library path; $self->{'default_sql_lib_path'}";
    my $filename;
    my $count = 0;
    while (defined ($filename = readdir(DIRHANDLE))){
	if ($filename =~ /.sql_lib$/){
	    $count++;
	    #print STDOUT "\nLibrary $count $self->{default_sql_lib_path}$filename\n\n";
            my $target_file = "$self->{'default_sql_lib_path'}" . "$filename";
	    unless (open FILE, "$target_file") { carp "Access denied to file $filename !"};
	    
	    # The user may want to see the SQL in the library
	    $/ = ""; # pull out a paragraph at at time
	    my $int_count = 0;
		
	    while (<FILE>){
		next unless ($_ =~ />(\S+)</);
		my @a = split /\n/,$_;
		my $sql;
		foreach my $n (@a){
		    next if $n =~ />(\S+)</;
		    my ($real, $comment) = split /\#/,$n,2;
		    next unless ($real =~ /\S+/);
		    $sql = $sql . "$real\n";
		}
	             
		if ($sql =~ /$string/){
                    print STDOUT "From the file $filename\n";
                    print STDOUT "----------------------------------\n";
		    print STDOUT "$sql\n";
		}
	    }
	    
	    close FILE;
	}
    }
    closedir(DIRHANDLE);


    $/ = "\n"; # good manners


}


### end test


###################################################
# We could potentially store our SQL in a database
# Expand on access methods to the SQL libraries
###################################################
sub available_sql_libs {
    my ($self, $mode) = @_;
    
    # Look in $self->{default_sql_lib_path} for *.sql_lib files
    opendir(DIRHANDLE, "$self->{'default_sql_lib_path'}") || croak "SQL_ENGINE_ERROR Cannot open default SQL library path; $self->{'default_sql_lib_path'}";
    my $filename;
    my $count = 0;
    while (defined ($filename = readdir(DIRHANDLE))){
	if ($filename =~ /.sql_lib$/){
	    $count++;
	    print STDOUT "\nLibrary $count $self->{'default_sql_lib_path'}$filename\n\n";
            my $target_file = "$self->{'default_sql_lib_path'}" . "$filename";
	    unless (open FILE, "$target_file") { carp "Access denied to file $filename !"};

	    # The user may want to see the SQL in the library
	    $/ = ""; # pull out a paragraph at at time
	    my $int_count = 0;
		
		while (<FILE>){
		    next unless ($_ =~ />(\S+)</);
		    my @a = split /\n/,$_;
		    $int_count++;
		    print STDOUT "------------------------------- $int_count -- $1\n";

		    if ($mode eq 'show_sql'){
			my $sql;
			foreach my $n (@a){
			    next if $n =~ />(\S+)</;
			    my ($real, $comment) = split /\#/,$n,2;
			    next unless ($real =~ /\S+/);
			    $sql = $sql . "$real\n";
			}
			print STDOUT "$sql\n";
		    }
		}

	    close FILE;
	}
    }
    closedir(DIRHANDLE);

    $/ = "\n"; # good manners
    
    if ($count == 0){
	carp "There are no SQL libraries (*.sql_lib) available in $self->{'default_sql_lib_path'}\n";
    }

}




sub load_sql_lib {
    my ($self, $libs) = @_;

    ###############################################################
    # Don't allow the caller to load (and hence prepare) SQL twice
    ###############################################################
    if (defined $self->{sql_libs}) { croak "SQL_ENGINE_ERROR SQL is already loaded !\n";}

    ############################################## 
    # Check that $libs is a reference to an array
    ##############################################
    unless (ref ($libs) eq "ARRAY") { croak "SQL_ENGINE_ERROR You must supply an array to load_sql_lib!\n";}


    my @get;
    my %seen_libs;
    my $count_libs = 0;
    foreach my $n (@$libs){

	unless ($n =~ /.sql_lib/){
	    croak "SQL_ENGINE_ERROR $n does not refer to a valid SQL library name";
	}
	
	##########################
	# Parse what we are given
	##########################
	my ($lib, $sql);
	if ($n =~ /::/){
	    ($lib, $sql) = $n =~ /(\S+)::(\S+)/;
	} else {
	    croak "SQL_ENGINE_ERROR $n is not in the correct format of library::sql_name | all\n";
	}

	############################################
	# User wants all the SQL within the library
	############################################
	if ($sql eq 'all'){
	    my $lives = $seen_libs{$lib};
	    
	    if (defined $lives && $lives ne ''){

		# clear any sql for this library that may have already been requested
		$get[$lives] = {};
#		%{$get[$lives]} = {};

		$get[$lives]->{$sql} =  "insert SQL here";
	    } else {

		$seen_libs{$lib} = $count_libs;
		$get[$count_libs]->{$sql} =  "insert SQL here";
		$count_libs++;
	    }

	    #######################################
	    # User wants specific SQL in a library
	    #######################################
	} else {
	    unless (defined $seen_libs{$lib}){
		$seen_libs{$lib} = $count_libs;
		$get[$count_libs]->{$sql} =  "insert SQL here";
		$count_libs++;
	    } else {
		my $lives = $seen_libs{$lib};
		$get[$lives]->{$sql} =  "insert SQL here";
	    }
	}
    }

    if ($self->{debug} == 1){
	warn Data::Dumper::Dumper(\@get);
    }
    
    ###############################################
    # We now need to get the SQL itself from file
    # Go through each requested library in turn
    ###############################################
    foreach my $n (keys %seen_libs){

	my $lives = $seen_libs{$n};

	##################################
	# Do we want the entire library ?
	##################################

	my $target_file = "$self->{'default_sql_lib_path'}" . "$n";
	if (defined $get[$lives]->{'all'}){

	    # dn1 change
	    $get[$lives] = {};
#	    %{$get[$lives]} = {};

	    unless (open FILE, $target_file) { croak "SQL_ENGINE_ERROR Access denied to file $target_file !"};
	    $/ = ""; # pull out a paragraph at at time
	    my $int_count = 0;
	    
	    while (<FILE>){

		next unless ($_ =~ />(\S+)</);
		my @a = split /\n/,$_;
		$int_count++;
		my $sql = "";
		foreach my $n (@a){
		    next if $n =~ />(\S+)</;
		    my ($real, $comment) = split /\#/,$n,2;
		    next unless ($real =~ /\S+/);
		    $sql = $sql . "$real\n";
		}

		$get[$lives]->{$1} = $sql;

	    }
	    close FILE;
	} else {

	    #################################################
	    # Only pull out specific SQL (are any missing ?)
	    #################################################
	    unless (open FILE, $target_file) { croak "SQL_ENGINE_ERROR Access denied to file $target_file !"};
	    $/ = ""; # pull out a paragraph at at time
	    my $int_count = 0;
	    
	    while (<FILE>){
		

		next unless ($_ =~ />(\S+)</);
		my @a = split /\n/,$_;
		$int_count++;
		my $sql;
		foreach my $n (@a){
		    next if $n =~ />(\S+)</;
		    my ($real, $comment) = split /\#/,$n,2;
		    next unless ($real =~ /\S+/);
		    $sql = $sql . "$real\n";
		}

		#####################################
		# Is this SQL we are interested in ?
		#####################################
		if (defined $get[$lives]->{$1}){
		    $get[$lives]->{$1} =  "$sql";
		}

	    }
	    close FILE;
	}
    }



    ##############################
    # Store the SQL in our object
    ##############################
    $self->{seen_libs} = \%seen_libs;
    $self->{sql_libs} = \@get;

    if ($self->{debug} == 1){
	warn "BEFORE PREP...\n";
	warn Data::Dumper::Dumper($self);
    }

    ###############################################################
    # We now need to prepare and store the SQL that we have loaded
    ###############################################################
    $self->prepare_sql_lib;

    if ($self->{debug} == 1){
	warn "AFTER PREP...\n";
	warn Data::Dumper::Dumper($self);
    }

    $/ = "\n"; # good manners

}

sub prepare_sql_lib {
    my $self = shift;

    ##########################
    # Go through each library
    ##########################
    foreach my $n (keys %{$self->{seen_libs}}){

	my $array_memb = $self->{seen_libs}->{$n};

	############################################
	# Go through each statement in each library
	############################################
	foreach my $ns (keys %{$self->{sql_libs}->[$array_memb]}){

	    my $sql = $self->{sql_libs}->[$array_memb]->{$ns};

	    if ($sql eq 'insert SQL here'){
		croak "SQL_ENGINE_ERROR $statement: SQL could not be retrieved for $ns !";
	    }

	    $self->{prepared_sql_libs}->[$array_memb]->{$ns} = $self->{DBH}->prepare("$sql") || croak "SQL_ENGINE_ERROR ERROR:$DBI::errstr";

	}
    }
}


sub do {
    my ($self, $statement, $binds) = @_;

    ###################################################################
    # Isolate the statement handle requested. Barf if it doesn't exist
    ###################################################################
    my ($lib, $sql);
    if ($statement =~ /::/){
	($lib, $sql) = $statement =~ /(\S+)::(\S+)/;
    } else {
	croak "SQL_ENGINE_ERROR $statement is not in the correct format of library::sql_name | all\n";
    }
    my $array_memb = $self->{seen_libs}->{$lib};
    unless (defined $self->{prepared_sql_libs}->[$array_memb]->{$sql}){
	croak "SQL_ENGINE_ERROR $statement: This SQL has not been prepared !";
    }

    eval {                          
	$self->{prepared_sql_libs}->[$array_memb]->{$sql}->execute(@$binds);
    };
    if($@){
	croak "SQL_ENGINE_ERROR ERROR $self sub insert :\n\n$@";
    }
    $self->{prepared_sql_libs}->[$array_memb]->{$sql}->finish;
}



sub get {
    my ($self, $statement, $binds) = @_;

    ###################################################################
    # Isolate the statement handle requested. Barf if it doesn't exist
    ###################################################################
    my ($lib, $sql);
    if ($statement =~ /::/){
	($lib, $sql) = $statement =~ /(\S+)::(\S+)/;
    } else {
	croak "SQL_ENGINE_ERROR $statement is not in the correct format of library::sql_name | all\n";
    }
    my $array_memb = $self->{seen_libs}->{$lib};
#    warn "'$lib' was not loaded (should be fatal, but isn't)" if !defined $array_memb;
    unless (
#	    defined $array_memb &&
# nb. fixing this will break code that has the wrong sql_lib name; there may be a lot...
# could detect "safe" cases and just issue a warning?
	    defined $self->{prepared_sql_libs}->[$array_memb]->{$sql}){
	croak "SQL_ENGINE_ERROR $statement: This SQL has not been prepared !";
    }

    eval {
	$self->{prepared_sql_libs}->[$array_memb]->{$sql}->execute(@$binds);
    };
    if($@){
	croak "SQL_ENGINE_ERROR $statement sub get :\n\n$@";
    }
    my $t = $self->{prepared_sql_libs}->[$array_memb]->{$sql}->fetchall_arrayref();
    $self->{prepared_sql_libs}->[$array_memb]->{$sql}->finish;
    return ($t);
}


sub end {
    my $self = shift;


    if ($self->{debug} == 1){
	warn "Ending connection for $self->{wrap_user_id}...\n";
    }

    if ($self->{debug} == 1){
	carp "Finishing all statements for $self->{wrap_user_id}";
    }
    foreach my $n (keys %{$self->{seen_libs}}){
	 my $array_memb = $self->{seen_libs}->{$n};
	 ############################################
	 # Go through each statement in each library
	 ############################################
	 foreach my $ns (keys %{$self->{sql_libs}->[$array_memb]}){
	     $self->{prepared_sql_libs}->[$array_memb]->{$ns}->finish;
	 }
    }

    if (defined $self->{DBH}){
	if ($self->{debug} == 1){
	    carp "Rolling back for $self->{wrap_user_id}";
	}
	$self->{DBH}->rollback();
	if ($self->{debug} == 1){
	    carp "Disconnecting for $self->{wrap_user_id}";
	}
	$self->{DBH}->disconnect();
    }
}


#sub DESTROY {
#    my $self = shift;
#
#    carp "Ending connection for $self->{wrap_user_id}...\n";
#
#    #############################################################
#    # Go through each library and finish all prepared statements
#    #############################################################
#    carp "Finishing all statements";
#    foreach my $n (keys %{$self->{seen_libs}}){
#	  my $array_memb = $self->{seen_libs}->{$n};
#	  ############################################
#	  # Go through each statement in each library
#	  ############################################
#	  foreach my $ns (keys %{$self->{sql_libs}->[$array_memb]}){
#	      $self->{prepared_sql_libs}->[$array_memb]->{$ns}->finish;
#	  }
#    }
#    
#    if (defined $self->{DBH}){
#	   carp "Rolling back";
#	   $self->{DBH}->rollback();
#	   carp "Disconnecting";
#	   $self->{DBH}->disconnect();
#     }
#    
#}



1;







