package MODEL_DB;

# Version 1.00 Original by Robert C. Groman

use 5.008003;
use strict;
use warnings;

my $table;

#----------------------
use DBI;

my $error = '&x';
# Connect usglobecdb.models database, database user readonly, do not need argument.

sub connect_model_login
{
    my $host = "localhost";
    my $user = "model_user";
    my $passw = "model_user_pw";
    my $database = "usglobecdb";
    my $dsh = "DBI:mysql:host=$host;database=$database";
    $table = 'models';
    my $dbh = DBI->connect($dsh,$user,$passw,{RaiseError=>0,PrintError=>0,autocommit=>0}) or die $DBI::errstr;
    return $dbh;
}


# Connect usglobecdb.models_purg database, test database user readonly, do not need argument.

sub connect_model_purgatory_login
{
    my $host = "localhost";
    my $user = "model_purg_user";
    my $passw = "model_purg_user_pw";
    my $database = "usglobecdb";
    my $dsh = "DBI:mysql:host=$host;database=$database";
    $table = 'models_purg';
    my $dbh = DBI->connect($dsh,$user,$passw,{RaiseError=>0,PrintError=>0,autocommit=>0}) or die $DBI::errstr;
    return $dbh;
}

#------------------------------------------------------------------

sub get_record_by_id {

# Get all the values from the appropriate table row, hopefully defined during
# the connect step for the id value specied as @_[1].  @_[0] is the value
# of $dbh defined by the calling program.  Returned value is the
# reference to the hash containing the data by column name.

	my ($dbh, $id) = @_;
	my $query = "select * from $table where id=?";
	my $sth = $dbh -> prepare($query);
	if (defined $DBI::errstr) {
		MODEL_DB::sendmessage($error, "Could not prepare SELECT for id-$id)",
			"Error=$DBI::errstr, and error code=$DBI::err");
		return "Failure";
	}
	$sth -> execute($id);
	if (defined $DBI::errstr) {
		MODEL_DB::sendmessage($error, "Could not execute query=$query)",
			"Error=$DBI::errstr, and error code=$DBI::err");
		return "Failure";
	}
	my $ref = $sth -> fetchrow_hashref();
	$sth -> finish ();
	return 'okay', $ref;
	
}

#-----------------------------------------------------------------------------
# Sendmessage routine

sub sendmessage {

# Send a message to the user, via e-mail and to STDOUT.
# The message sent will be in the strings $_[1], $_[2], ...
# The prefix string is contained in $_[0].

my ( @args, $i, $mailfile, $message0, $message1, $prefix, $who);
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst);

my $error = '&x';
my $warning = '#';

$prefix=$_[0];
unless (defined $prefix) {
	$prefix = "&x";
}

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
if ($year >= 100 and $year <= 1000) {$year = $year + 1900}
if ($year < 1900) { $year = $year + 2000; }
$mon++;
if ($mon < 10) { $mon = "0" . $mon; }
if ($mday < 10) {$mday = "0" . $mday; }
if ($hour < 10) { $hour = "0" . $hour; }
if ($min < 10) { $min = "0" . $min; }
if ($sec < 10) { $sec = "0" . $sec; }
undef $wday;
undef $isdst;
$mailfile="> /tmp/sendmess" . $year . $yday . $hour . $min . $sec . ".tmp";

if ( open TEMPFILE, $mailfile) {
	print TEMPFILE ("Message from $0\n");
	if ( exists $ENV{'REMOTE_HOST'} ) {$who=$ENV{'REMOTE_HOST'} ; }
	elsif (exists $ENV{'REMOTE_ADDR'} ) {$who=$ENV{'REMOTE_ADDR'} ; }
	else {$who="not available"; }
	print TEMPFILE (" Date of message: $year/$mon/$mday $hour:$min\n");
	print TEMPFILE (" From: $who\n");
	for ($i=1; $i<=$#_; $i++) {
		print TEMPFILE ($_[$i], "\n");
	}
	close TEMPFILE;
	`mail -s "Problem with $0" rgroman\@whoi.edu <$mailfile`;
	unlink $mailfile;
}

for ($i=1; $i<=$#_; $i++) {
	print STDOUT ($_[$i], "\n");
}
print STDOUT ($prefix," Above message from $0\n");
print STDOUT ($prefix," Date of message: $year/$mon/$mday $hour:$min\n");
return;
}

#-----------------------------------------------------------------------
sub fast_search_of_table {

# fast_search_of_table

# Search the table specified as follows:
#	_[0] - $dbh value from calling routine
#	_[1] - table name to search
#	_[2,3,4,...] - search strings to look for
# If the search string is empty or blank, just return.
# If search string is set to % then matches everything.
# On completion, it returns 
#	status = 'okay' or an error message
#	ids_ref = reference to array of id numbers for
#			matches found

# Assumptions:
#	1. $dbh is defined by calling routine and passed as _[0].
#	2. The column "id" is the primary key in the table.
#	3. Currently the model "database" consistes of two tables,
# the "real" one called models and the models_purg for the
# temporary, unverified, one.
	   

my $dbh = $_[0];
my $table = $_[1];
my @search_strings;
for (my $i=2; $i<=$#_; $i++) {
	$search_strings[$i-2] = $_[$i];
}


#####################################################################################
my $debug = 'no';
#####################################################################################

print STDOUT (
		"<p>***debug, in fast_search_of_table, \n",
		"<br>table=$table, ", 
		"<br>search strings=@search_strings<p>\n")
	if $debug eq 'yes';


unless ($search_strings[0] =~ m/\w{1,}/ ) {return 'Okay:'}
if ($search_strings[0] eq ''  or $search_strings[0] eq ' ') {return 'Okay:'}

print STDERR ("***debug, $0, just before show columns from $table\n")
	if $debug eq 'yes';

my $ref = $dbh->selectcol_arrayref ("SHOW COLUMNS FROM $table");
print STDOUT (
		"<p>***debug, after call to show columns\n",
		"Error string=$DBI::errstr, and ",
		"error=$DBI::err </p>\n") 
	if $debug eq 'yes';

my @field_names = @{$ref} if defined $ref;

unless (defined $ref) {
	MODEL_DB::sendmessage($error, 
        	"Could not get field names for table $table.",
		"Error string=$DBI::errstr, and error=$DBI::err");
	return "Error, could not get field names for table=$table";
}

my $select_clause;
$select_clause = 'select id, name from ' . $table . " where (";

foreach my $field (@field_names) {
	if ($field eq 'id' or $field eq 'local_id'
		or $field =~ m/'modified_date'/i) {next}
	foreach my $search_string (@search_strings) {
		$select_clause = $select_clause . $table . '.' . $field . 
			' LIKE ' . "'%" . $search_string . "%' or ";
	}
}

$select_clause =~ s/ or $/)/;

print STDERR ("\n<p>***debug, select_clause=$select_clause<p>\n")
	if $debug eq 'yes';

my $sth = $dbh->prepare($select_clause);

if (defined $DBI::errstr) {
	MODEL_DB::sendmessage($error, 
		"<p>Could not prepare SELECT using=$select_clause",
		"<br>Error string=$DBI::errstr, and error=$DBI::err");
	return "Error=$DBI::errstr, could not prepare SELECT using=$select_clause";
}

$sth->execute();
if (defined $DBI::errstr) {
	MODEL_DB::sendmessage($error, 
		"<p>Could not do execute using=$select_clause",
		"<br>Error string=$DBI::errstr, and error=$DBI::err");
	return "Error=$DBI::errstr, could not do execute using=$select_clause";
}

my (@ids, @species_names);
my $i = -1;

while (my @val = $sth->fetchrow_array() ) {
	print STDOUT ('<p>***debug, in fast_search_of_table, ',
		" val[0]=$val[0]<br>\n") 
			if $debug eq 'yes';
	$i++;
	$ids[$i] = $val[0];
}
	

$sth->finish() if defined $sth;

return 'okay', \@ids;
}

#----------------------------------------------------------------------------

sub parse_search_terms {

# Given a variable containing the list of search terms, $_[0], parse
# them into an array where elements of the array are the individual
# search words and phrases.  Words in single or double quotes are
# search for as combined phrases.  Note that $_[1] is the value of
# the debug flag, either 'yes' or 'no'.  If not defined, then set to
# 'no'.

my ($begin_pos, $debug, $end_pos, @keywords, 
	$length, $lookfor, $pos, $string, 
	@strings, $strings);
my $search_terms = $_[0];
if (defined $_[1]) {
	$debug = $_[1];
}
else {
	$debug = 'no';
}
if ($search_terms =~ m/\w+/) {
	$strings = $search_terms;
	$lookfor = '"';
	$pos = -1;
	while (($pos = index($strings, $lookfor, $pos)) > -1) {
		$pos++;
		$begin_pos = $pos;
		$end_pos = $pos;
		$end_pos = index($strings, $lookfor, $pos);
		unless ($end_pos > 0) {
			print STDERR (
				"$0, missing closing $lookfor, string ignored\n");
			last;
		}
		$length = ($end_pos - $begin_pos);
		push @strings, substr $strings,$begin_pos,$length;
		$pos = $end_pos + 1;
	}
	$search_terms =~ s/"//g;
	foreach $string (@strings) {
		$search_terms =~ s/$string//;
	}
	$search_terms =~ s/,{2,}/,/g;
	$search_terms =~ s/^,//;
	$search_terms =~ s/,$//;

	print STDOUT ("<p>***debug, lookfor=$lookfor, strings=@strings<br>\n")
		if $debug eq 'yes';
	
	print STDOUT (
	  	"<p>***debug, after $lookfor search ",
		"param{'keyword_search'}=$search_terms<br>\n")
		if $debug eq 'yes';
	
	$strings = $search_terms;
	$lookfor = "'";
	$pos = -1;
	while (($pos = index($strings, $lookfor, $pos)) > -1) {
		$pos++;
		$begin_pos = $pos;
		$end_pos = $pos;
		$end_pos = index($strings, $lookfor, $pos);
		unless ($end_pos > 0) {
			print STDERR (
				"$0, missing closing $lookfor, string ignored\n");
			last;
		}
		$length = ($end_pos - $begin_pos);
		push @strings, substr $strings,$begin_pos,$length;
		$pos = $end_pos + 1;
	}
	
	print STDOUT ("<p>***debug, all quoted strings=@strings<br>\n")
		if $debug eq 'yes';
	
	$search_terms =~ s/'//g;
	#Remove strings from search string
	foreach $string (@strings) {
		$search_terms =~ s/$string//;
	}

	print STDOUT (
	  	"<p>***debug, after $lookfor search ",
		"param{'keyword_search'}=$search_terms<br>\n")
		if $debug eq 'yes';

	$search_terms =~ s/,+/,/g;
	$search_terms =~ s/ +/ /g;
	$search_terms =~ s/^,//;
	$search_terms =~ s/,$//;
	$search_terms =~ s/^ +//;
	$search_terms =~ s/ +$//;
	$search_terms =~ s/,/ /g;
	$search_terms =~ s/_(\w)/ $1/g;
	$search_terms =~ s/ +/ /g;
	$search_terms =~ s/_$//g;

	print STDOUT (
	  	"<p>***debug, after substitutions ",
		"param{'keyword_search'}=$search_terms<br>\n")
		if $debug eq 'yes';

	@keywords = split / /, $search_terms;
	
	if (defined $strings[0]) {
		for (my $i=0; $i<=$#strings; $i=$i+1) {
			print STDOUT ("<p>***debug, strings[$i]=$strings[$i]<br>\n")
				if $debug eq 'yes';
			if ($strings[$i] =~ m/^_/) {$strings[$i] =~ s/^_//g;}
			if ($strings[$i] =~ m/_$/) {$strings[$i] =~ s/_$//g;}
			if ($strings[$i] =~ m/_/) {$strings[$i] =~ s/_/ /g;}
			if ($strings[$i] =~ m/ +/) {$strings[$i] =~ s/ +/ /g;}
		}
		push @keywords, @strings;
	}
	unless (defined $keywords[0] and $keywords[0] =~ m/\w+/ ) {
		$keywords[0] = $search_terms;
	}
	print STDOUT ("<p>***debug, keywords=@keywords<br>\n")
		if $debug eq 'yes';
}
return @keywords;
}

1;