#!/project/bioperl/perl-5.10.1-sles11/bin/perl -w  

## 
#
# This file requires that a database connection be configured and
#  connected to the scalar $dbh
#
##

if (!defined($project_table_name)) { 
    $project_table_name = "TAPDANCE_project_master";
}
return 1;

sub project_table_check {
    my $create_string = "CREATE TABLE IF NOT EXISTS " . $project_table_name . " (name VARCHAR(255) NOT NULL, insert_gen TIMESTAMP, cis_call TIMESTAMP)";
    my $sth = $dbh->prepare($create_string);
    $sth->execute;
}


sub project_exists {
    my ($proj_name) = @_;
    &project_table_check();
    my $test_string = "SELECT COUNT(name) FROM " . $project_table_name . " WHERE name = '" . $proj_name . "'";
    my $sth = $dbh->prepare($test_string);
    $sth->execute;
    (@row) = $sth->fetchrow_array;
    return $row[0] > 0;
}

sub update_project {
    &project_table_check();
    my ($proj_name, $insert_gen, $cis_call) = @_;
    my $update_string = "UPDATE " . $project_table_name . " SET";
    my $prefix = " ";
    if ($insert_gen) {
	$update_string = $update_string . $prefix . "insert_gen = NOW()";
	$prefix = ", "
    }
    if ($cis_call) {
	$update_string = $update_string . $prefix . "cis_call = NOW()";
    }
    $update_string = $update_string . " WHERE name = '" . $proj_name . "'";
    my $sth = $dbh->prepare($update_string);
    $sth->execute;
}

sub insert_project {
    &project_table_check();
    my ($proj_name, $insert_gen, $cis_call) = @_;
    my $insert_string = "INSERT INTO " . $project_table_name;
    my $insert_cols_string = " (name";
    my $insert_vals_string = " VALUES('" . $proj_name . "'";
    if ($insert_gen) {
	$insert_cols_string = $insert_cols_string . ", insert_gen";
	$insert_vals_string = $insert_vals_string . ", NOW()";
    }
    if ($cis_call) {
	$insert_cols_string = $insert_cols_string . ", cis_call";
	$insert_vals_string = $insert_vals_string . ", NOW()";
    }
    $insert_cols_string = $insert_cols_string . ") ";
    $insert_vals_string = $insert_vals_string . ") ";
    $insert_string = $insert_string . $insert_cols_string . $insert_vals_string;
    my $sth = $dbh->prepare($insert_string);
    $sth->execute;
}

sub delete_project {
    &project_table_check();
    my ($proj_name) = @_;
    my $delete_string = "DELETE FROM " . $project_table_name . " WHERE name = '" . $proj_name . "'";
    my $sth = $dbh->prepare($delete_string);
    $sth->execute;
}

sub set_project_status {
    my ($proj_name, $insert_gen, $cis_call) = @_;
    if (&project_exists($proj_name)) {
	&update_project($proj_name, $insert_gen, $cis_call);
    } else {
	&insert_project($proj_name, $insert_gen, $cis_call);
    }
} 

sub get_project_list {
    my ($user_name, $query_type, @tags) = @_;
    &project_table_check();
    my $select_string = "SELECT name FROM " . $project_table_name . " WHERE name LIKE '" . $user_name . "%'";
    my $sth = $dbh->prepare($select_string);
    $sth->execute();
    my @projects = ();
    while ((@row) = $sth->fetchrow_array) {
	if ($query_type eq "all") {
	    push @projects, $row[0];
	} else {
	    my $conjunctive;
	    #if ($query_type eq "union") {
		#$conjunctive = "AND";
	    #} elsif ($query_type eq "join") {
		$conjunctive = "OR";
	    #}
	    $select_string = "SELECT COUNT(*) from metadata_$row[0] where descriptor LIKE '%";
	    $select_string = $select_string . join("%' " . $conjunctive . " descriptor LIKE '%", @tags) . "%'";
	    #print $select_string . "\n";
	    my $sth2 = $dbh->prepare($select_string);
	    $sth2->execute;
	    while ((@row2) = $sth2->fetchrow_array) {
		if ($row2[0] > 0) {
		    push @projects, $row[0];
		}
	    }
	}
    }
    return \@projects;
}