#!/usr/bin/perl

###############################################################
#  Written by Justin Elser 2/22/10                            #
#                                                             #
#  This program takes all fasta files in the current working  #
#    directory and puts them in a database                    #
#                                                             #
#  Initial version 0.1                                        #
#     Shouldn't have to change much once this is done         #
#         except to maybe add more species detection          #
#         which is actually done in the pulled in subs        #
#                                                             #
#  version 0.9                                                #
#      Switched to using the DbiFloret::dbconnect to manage   #
#        the connection to the DB.  Also added a section to   #
#        put in the version info into a new table.            #
#        Can't think of much more that would need to go in    #
#        here.                                                #
#                                                             #
###############################################################

use strict;
use warnings;

use DBI;
use Term::Screen::ReadLine;

# pull in the find_species and find_gene common subroutines
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";
}

use lib "$ENV{HOME}/scripts/jaiswallab/interactome_scripts";

use DbiFloret;

my $dbh = DbiFloret::dbconnect_local;

my @files = glob("*.fa");

foreach my $file (@files) {
	my $species = find_species($file);
	print "on species $species\n";
	
	my ($retrieval_location, $retrieval_date) = retrieval_info($file);
	$retrieval_date =~ /(\d+)\_(\d+)\_(\d\d)/;
	my $formatted_date = "20".$3."-".$1."-".$2;
	
	my $total_genes = 0;
	my $canonical_genes = 0;
	
	# insert version info into database.  If species previously entered, location and date will be updated.
	my $safe_info_table = $dbh->quote_identifier("version_info");
	# Create the database for version info if it doesn't already exist
	$dbh->do("CREATE TABLE IF NOT EXISTS $safe_info_table (
			`species` varchar(100) NOT NULL,
			`retrieval_location` varchar(255) NOT NULL,
			`date` date NOT NULL,
			PRIMARY KEY (`species`)
			) ENGINE=MyISAM");
	# Create the table for isoform info if it doesn't already exist
	my $safe_isoform_table = $dbh->quote_identifier("isoform_info");
	$dbh->do("CREATE TABLE IF NOT EXISTS $safe_isoform_table (
			`species` varchar(100) NOT NULL,
			`total_genes` int(11) NOT NULL,
			`canonical_genes` int(11) NOT NULL,
			PRIMARY KEY (`species`)
			) ENGINE=MyISAM");


	$dbh->do("insert into $safe_info_table (species,retrieval_location,date) values ('$species','$retrieval_location','$formatted_date') on duplicate key update retrieval_location='$retrieval_location', date='$formatted_date'");
	
	# create the database table for the species
	my $safe_table = $dbh->quote_identifier($species);
	
	# create the database table for all synonyms
	my $safe_syn_table = $dbh->quote_identifier('synonyms');
	
	$dbh->do("drop table if exists $safe_table");
	$dbh->do("CREATE TABLE $safe_table (
        	`gene_id` VARCHAR( 100 ) NOT NULL ,
		`gene_header` TEXT NOT NULL ,
		`sequence` TEXT NOT NULL ,
		UNIQUE ( `gene_id` )
		) ENGINE = MYISAM");

	$dbh->do("CREATE TABLE if NOT EXISTS  $safe_syn_table(
			`gene_id` VARCHAR( 100 ) NOT NULL ,
			`species` VARCHAR( 100 ) NOT NULL ,
			`synonyms` TEXT ,
			UNIQUE KEY `gene_species_idx` (`gene_id`, `species`)
			) ENGINE = MYISAM");

	# and the statement handler to do the inserts
	my $insert_sth = $dbh->prepare("insert into $safe_table (gene_id, gene_header, sequence) values (?,?,?)");
	my $insert_syn_sth = $dbh->prepare("replace into $safe_syn_table (gene_id, species, synonyms) values (?,?,?)");
	my $insert_counts_sth = $dbh->prepare("replace into $safe_isoform_table (species, total_genes, canonical_genes) values (?,?,?)");
	
	# create the variables
	
	my %seq_hash; # keys is $gene, values are $seq and $gene_header
	my $gene;
	my $seq;
	my $gene_header;
	my $synonyms;
	
	open(in_file,$file);
	
	while(<in_file>) {
		my $line = $_;
		chomp $line;
		if($line =~ /^\>/) {
			if(defined($gene)) {
				$seq_hash{$gene}->{'sequence'} = $seq;
				$seq_hash{$gene}->{'gene_header'} = $gene_header;
				if(defined($synonyms)) {
						$seq_hash{$gene}->{'synonyms'} = $synonyms;
						undef $synonyms;
				}
			}
			$gene_header = $line;
			$gene_header =~ s/^>//;
			$gene = find_gene($gene_header,$species);
			$gene =~ s/^\>//;
			$synonyms = find_gene_synonym($gene_header,$species);
			$seq = "";
			if(trans_filter($species, $gene)) {
					$canonical_genes++;
			}
			$total_genes++;
		}else{
			$seq = "$seq"."$line";
		}
	}
	
	# add the last gene to the hash
	$seq_hash{$gene}->{'sequence'} = $seq;
	$seq_hash{$gene}->{'gene_header'} = $gene_header;
	if(defined($synonyms)) {
			$seq_hash{$gene}->{'synonyms'} = $synonyms;
	}

	foreach my $key (keys %seq_hash) {
		$insert_sth->execute($key,$seq_hash{$key}->{'gene_header'},$seq_hash{$key}->{'sequence'});

		if($seq_hash{$key}->{'synonyms'} ne "null") {
				$insert_syn_sth->execute($key,$species,$seq_hash{$key}->{'synonyms'});
		}
	}
	$insert_counts_sth->execute($species,$total_genes,$canonical_genes);
}



