#!/usr/bin/perl

###############################################################
#  Justin Elser  (elserj@science.oregonstate.edu)             #
#        Parses the output from an inparanoid run and         #
#           enters it into a database                         #
#                                                             #
#  Version 1.00 - September 2009                              #
#     Seems to work fine                                      #
#  Version 1.01 - Nov 4 '09                                   #
#     Added support for strawberry                            #
#  Version 1.1 - April 2010                                   #
#     Changed to use external subs to find species            #
#                                                             #
###############################################################


use strict;
use warnings;

use DBI;

use Getopt::Long;
my $new = '';
my $strip = '';
GetOptions ('new' => \$new, 'strip' => \$strip);


# set up the database connection
use lib "$ENV{HOME}/scripts/jaiswallab/interactome_scripts";

use DbiFloret;

my $dbh = DbiFloret::dbconnect;

#if($new) {
#		my $database = $dbh->selectrow_array("select DATABASE()");
#		$dbh->do("drop database if exists $database");
#		$dbh->do("create database $database");
#}


if(-e "$ENV{HOME}/scripts/jaiswallab/interactome_scripts/find_species.pl") {
	require "$ENV{HOME}/scripts/jaiswallab/interactome_scripts/find_species.pl";
}elsif(-e "$ENV{HOME}/bin/find_species.pl") {
	require "$ENV{HOME}/bin/find_species.pl";
}

# open the file to be parsed, assume this is all sqltable* files in directory

my @files = glob("sqltable*");


		
		
my $clust_table = "clusters";
my $safe_clust_table = $dbh->quote_identifier($clust_table);

my %id_hash;
if($new) {
	$dbh->do("drop table if exists $safe_clust_table");
	$dbh->do("create table $safe_clust_table (
		`id` INT( 11 ) NOT NULL AUTO_INCREMENT,
		`clust_id` VARCHAR( 255 ) NOT NULL ,
		INDEX ( `id` )
		) TYPE = MYISAM");
}else{ # if not new load, grab the data from the clusters table
	my $sth_id_dump = $dbh->prepare("select * from clusters");
	# put the clusters table data into a hash
	print "before dump\n";
	$sth_id_dump->execute();
	print "after dump\n";
	while(my @ids = $sth_id_dump->fetchrow_array) {
		if(!defined($id_hash{$ids[1]})) {
			$id_hash{$ids[1]} = $ids[0];
		}
	}
}
		
my $tot_file_count = scalar @files;
my $file_counter = 1;
	
# start the database entries
foreach my $input_file (@files) {
	open(in_file, "$input_file") || die "Error: file '$input_file' can not be opened\n";

	my %clust_hash;

	

	my ($species_1, $species_2) = split("fa-", $input_file);
	$species_1 = find_species($species_1);
	$species_2 = find_species($species_2);
	my $species_table = $species_1."_".$species_2;
	

	
	print "Working on $species_table: file $file_counter/$tot_file_count\n";
	$file_counter++;


	
	my $safe_species_table = $dbh->quote_identifier($species_table);
	$dbh->do("drop table if exists $safe_species_table");
	$dbh->do("CREATE TABLE $safe_species_table (
		`id` INT( 11 ) NOT NULL ,
		`bit_score` INT( 11 ) NOT NULL ,
		`species` VARCHAR( 255 ) NOT NULL ,
		`score` FLOAT( 11 ) NOT NULL ,
		`gene` VARCHAR( 255 ) NOT NULL ,
		INDEX ( `id` )
		) TYPE = MYISAM") or die $dbh->errstr;;
	my $sth = $dbh->prepare("insert into $safe_species_table (id, bit_score, species, score, gene) values (?,?,?,?,?)");

	
		
	
	my $sth_id_add = $dbh->prepare("insert into $safe_clust_table (id, clust_id) values (?,?)");
	my $sth_get_id = $dbh->prepare("select last_insert_id(id) from clusters order by id desc limit 1");

	
	
	my $clust_id_prev = "";

	while(<in_file>) {
		my $entry = $_;
		# strip off newline characters
		$entry =~ s/\r//g;
		$entry =~ s/\n//g;
	
		my $id;
	
		my ($clust_id, $bit_score, $species, $score, $gene) = split ("\t", $entry);
	
		$clust_id = "$species_1"."___".$species_2."___".$clust_id;
		$species = find_species($species);
		# the next line is no longer neccessary, future inparanoid runs will fix the gene headers before running
		#my $gene = find_gene($gene_header,$species);
		# skip isomers that are not .1 if asked
		if ($strip) {
				if($species ne "Jatropha_curcas") { #Jatropha gene ids are .10, .20, .30, etc...
					next if ($gene =~ /\.[2-9]$/);
					next if ($gene =~ /\.1[0-9]$/);
					next if ($gene =~ /\.2[0-9]$/);
					next if ($gene =~ /\.3[0-9]$/);
					next if ($gene =~ /\.4[0-9]$/);
					next if ($gene =~ /\.5[0-9]$/);
					next if ($gene =~ /\.6[0-9]$/);
					next if ($gene =~ /\.7[0-9]$/);
					next if ($gene =~ /\.8[0-9]$/);
					next if ($gene =~ /\.9[0-9]$/);
				}
				
				# skip Maize genes that are not the first transcript
				if($species eq "Zea_mays") {
						next if ($gene !~ /\_T001$/);
				}
				
				# skip Theobroma genes that are not the first transcript
				if($species eq "Theobroma_cacao") {
						next if ($gene =~ /t[2-9]$/);
						next if ($gene =~ /t1[0-9]$/);
				}
		}
	
		if(!defined($id_hash{$clust_id})) {
			if ($clust_id ne $clust_id_prev) {
				$sth_id_add->execute(undef, $clust_id);
				$sth_get_id->execute();
				my @new_id = $sth_get_id->fetchrow_array;
				$id = $new_id[0];
				$id_hash{$clust_id} = $id;
			}
		}else {
			$id = $id_hash{$clust_id};
		}
	
		
		$sth->execute($id, $bit_score, $species, $score, $gene);
		$clust_id_prev = $clust_id;

		
	}
	$sth_get_id->finish();
}


$dbh->disconnect;
	

