#!/usr/local/bin/perl -w

#$Id$

use DBI;
use Test::More; 
use Digest::MD5 qw( md5 md5_base64 );
use strict;

use FindBin qw($Bin);
use lib( "$Bin/../.." );
use SBEAMS::Connection;
use SBEAMS::Connection::Tables;

# Number of times to execute each statement.
use constant ITERATIONS => 50; # Number of times to exec each query
use constant REFRESH_HANDLE => 1; # Refresh handle between executions?
use constant VERBOSE => 0;  # Level of loquaciousness to exhibit
use constant USE_SUBSELECT => 1;  # Use more complicated query
use constant NUM_ROWS => 100;  # (Max) Number of rows to hash together

# Quiet down in there!
close(STDERR);

# Immediate gratification desired, do not buffer output
$|++; 

my $subselect_query =<<END;
SELECT MGED_ontology_term_id, ontology_term_type_id, name, definition, uri,
       external_database_release_id, source_id, MGED_ontology_relationship_id,
       subject_term_id, predicate_term_id, object_term_id,
       ontology_relationship_type_id    
FROM ( SELECT * FROM $TB_MGED_ONTOLOGY_TERM MOT 
                JOIN $TB_MGED_ONTOLOGY_RELATIONSHIP MOR 
                ON MOR.subject_term_id = MOT.MGED_ontology_term_id  ) 
AS temp_table
END

my %queries = ( 1 => "SELECT * FROM $TB_ORGANISM",
                2 => "SELECT * FROM $TB_MGED_ONTOLOGY_RELATIONSHIP",
                3 => "SELECT * FROM $TB_MGED_ONTOLOGY_TERM",
                4 => "SELECT * FROM $TB_DBXREF",
              ); 

$queries{5} = $subselect_query if USE_SUBSELECT;

my $num_tests = scalar(keys(%queries)) * 2 + 5;
plan( tests => $num_tests );
  
# Set up user agent and sbeams objects
my $sbeams = SBEAMS::Connection->new();
my $dbh = dbConnect();

my $msg = ( ref($dbh) ) ?  "Connect to db ($dbh->{Driver}->{Name}, version $dbh->{Driver}->{Version}  )" : "Failed to connect to database";
ok( ref($dbh), $msg ); 

# Setup
my %results;


SKIP: {
skip "queries, db connection failed", $num_tests - 1 unless ref($dbh);
# Establish baseline data.
for my $key ( sort( keys( %queries ) ) ) { 
  my $sth = $dbh->prepare( $queries{$key} );
  $sth->execute;
  my @results = stringify( $sth );
  $results{$key} = \@results;
  ok( $results{$key} , "Got data for query $key" );
}


# Loop through each query and execute it the specified number of times.
my $status = 1;
my $iterations = ITERATIONS;
for my $key ( sort( keys( %queries ) ) ) {

  for( my $i = 1; $i < $iterations; $i++ ) {

    # Get a fresh handle, if so configured 
    $sbeams->setNewDBHandle() if REFRESH_HANDLE;

    # prep and exec query
    my $sth = $dbh->prepare( $queries{$key} );
    $sth->execute();

    # Check number and content of return values
    my( $num, $string ) = stringify( $sth );

    # Define error conditions
    if ( $num != $results{$key}->[0] ) {
      print STDERR "$num results returned, $results{$key} expected at iteration $i for query $key\n";
      $status = 0;
      last;
    } elsif ( $string ne $results{$key}->[1] ) {
      print STDERR "MD5 sum different at iteration $i for query $key\n";
      $status = 0;
      last;
    }
  }
  ok( $status, "Run query $key for $iterations iterations" );
}

my $ids;
my $num_rows = getNumRows();
ok( $ids = testInserts(), "Test inserting rows" );

my $new_num_rows = getNumRows();
ok( $num_rows + ITERATIONS == $new_num_rows, "Check number of rows: $new_num_rows" );

ok( cleanupInserts( $ids ), "Test deleting rows" );
$new_num_rows = getNumRows();
ok( $num_rows == $new_num_rows, "Check number of rows: $num_rows" );

} # End skip block
eval { $dbh->disconnect() };

sub getNumRows {
  my ( $num ) = $sbeams->selectrow_array( <<"  END_SQL" );
  SELECT COUNT(*) FROM $TB_TEST_SAMPLE
  END_SQL
  return $num;
}

sub testInserts {
  my @ids;
  my $name = 'sbeams_test_data.1';

  my ( $project_id ) = $sbeams->selectrow_array( <<"  END_SQL" );
  SELECT MIN(project_id) FROM $TB_PROJECT
  END_SQL

  $project_id ||= 1;

  for ( my $i = 0; $i < ITERATIONS; $i++ ) {

    # Get a fresh handle, if so configured 
    $sbeams->setNewDBHandle() if REFRESH_HANDLE;

    my $id = $sbeams->updateOrInsertRow( insert => 1,
                                      return_PK => 1,
                           add_audit_parameters => 1,
                                     table_name => $TB_TEST_SAMPLE,
                                    rowdata_ref => { project_id => $project_id,
                                                     sample_tag => $name,
                                                            age => $name,
                                       sample_protocol_ids => '1,2,3,4,5,6,7,8',
                                          sample_description => 'autogenerated'}
                                       );
    push @ids, $id;
    $name++; 
  }
  return \@ids;
}
sub cleanupInserts {
  my $ids = shift;
  return 0 unless ref( $ids );
  foreach my $id ( @$ids ) {
    $sbeams->do( "DELETE FROM $TB_TEST_SAMPLE WHERE sample_id = $id" ) || return 0;
  }
  return 1;
}

#+
# Join each row on '::', concatenate the whole shebang, and take an MD5Sum of the result.
#-
sub stringify {
  my $sth = shift;
  my $cnt = 0;
  my $contents = '';
  while ( my @row = $sth->fetchrow_array() ) {
    $cnt++;

    # Insurance against big tables!
    last if $cnt >= NUM_ROWS;
    $contents .= join "::", map{ ( defined $_ ) ? $_ : 'NULL' } @row;
  }
  my $chksum = md5_base64( $contents );
  print "$cnt rows, checksum => $chksum\n" if VERBOSE; # Anything happening?
  return ( $cnt, $chksum );
}

sub dbConnect {
  # We will use the sbeams connection machinery, connecting as a read_only
  # user.  If this is not set up, the connex will fail.
  
  my $status = $sbeams->Authenticate( connect_read_only => 0 );

  return $status unless $status;
  my $dbh = $sbeams->getDBHandle();

  return $dbh;
}