#!/usr/bin/perl -w
use strict;
use lib "../Perl/modules";
use lib "/usr2/people/khuang/bin/module";
use DBI;
use Genome;
use GenomicsUtils;
use SQL;
use Getopt::Long;



my $helpStr = qq{
    metabolite.pl <taxId> (options)

	(options):
	-noweb: output plain text
	-meta: output sorted by metabolites alphabetically 
	-mw: output sorted by metabolites MW
	-host: default is local host

};

unless (@ARGV>=1){die "$helpStr";}
my $noweb = 0;
my $sort = 0;
my $sortByMW = 0;
my $host;
GetOptions( 'noweb' => \$noweb,
	    'meta' => \$sort,
	    'mw' => \$sortByMW,
	    'host=s' => \$host
);

connect($host);

#setDebug(10);
my ($taxId) = $ARGV[0];
my $genome = Genome::new(taxonomyId=>$taxId);
my $sqlTemp = qq{
    SELECT distinct(ecNum)
	FROM Locus2Ec
	WHERE scaffoldId=SCA
};
my %ec = ();
my @scaffolds = $genome->scaffolds();
foreach (@scaffolds){
    my $sId = $_->scaffoldId();
    (my $sql = $sqlTemp) =~ s/SCA/$sId/;
    my $ecR = GenomicsUtils::query($sql);
    foreach (@$ecR){
	if ( $_->[0] ){
	    $ec{$_->[0]}++ unless ($_->[0]=~/-/);
	}	
    }
}
my %BioCyc = ();
my %meta = ();
my %meta2ec = ();
my %mw = ();
my %metaId = ();
my $token = 0;
&fetchBioCyc(\%BioCyc, \%metaId);
my $out = "";
map{
    my $ec = $_;
    if ( $BioCyc{$ec} ){
	$token++;
	map{
	    my $rxn = $_;
	    map{ my ($name, $mw) = split /\t/; $meta{$name}++; $mw{$name}= $mw; push @{ $meta2ec{$name} }, $ec; } @{ $BioCyc{$ec}{$rxn}{LEFT} };
	    map{ my ($name, $mw) = split /\t/; $meta{$name}++; $mw{$name}= $mw; push @{ $meta2ec{$name} }, $ec; } @{ $BioCyc{$ec}{$rxn}{RIGHT} };
	}sort keys %{ $BioCyc{$ec} };

    }

} sort keys %ec;

my $taxName = $genome->name();


if ($noweb){
    print scalar (keys %ec)." EC in $taxName\n";
    print scalar (keys %meta)." metabolites in $taxName\n";
    print "Metabolites in $token reactions\n";
   
    map{print join "\t", $_, $meta{$_}, @{$meta2ec{$_}}, "\n"} sort{$meta{$b} <=> $meta{$a}} keys %meta;
    #map{print join "\t", $_, $meta{$_}, $mw{$_}, "\n"} sort{$meta{$b} <=> $meta{$a}} keys %meta;
}
else{
   
    print scalar (keys %ec)." EC in $taxName\n<BR>";
    print scalar (keys %meta)." metabolites in $taxName\n<BR>";
    print qq{<HTML>
		 <HEAD>
		 <TITLE>$taxName Metabolites</TITLE>
		 <link rel="stylesheet" type="text/css" href="/ns.css" />
		 </HEAD>
		 <BODY>
		 <DIV CLASS="Head"><i>$taxName</i> Metabolites in $token reactions</DIV><BR>
		 <DIV CLASS="text10">
	     };

  
    print qq{
	
		 <TABLE width=100% border=1 bordercolor="#336699" cellspacing=0 cellpadding=0>
		 <TR>
		 <TD><B><A HREF="/metabolites.$taxId.a.html">Metabolite</A></TD>
		 <TD><B><A HREF ="/metabolites.$taxId.html">Occurence</A></TD>
		 <TD><B><A HREF ="/metabolites.$taxId.mw.html">MW</A></TD>
		 <TD><B>ECs</TD>
	
		 </TR>
	     };
    if ($sort){

	
	map{
	    my $link = $_;
	    map {$_ = "<A HREF=\"http://escalante.lbl.gov/cgi-bin/fetchEC.cgi?ec=$_&taxId=$taxId\" target=\"_ec\">$_</A>";} @{ $meta2ec{$link} };
	    my $ecs = join " ,", @{$meta2ec{$_}};
	    $link = "<A HREF=\"http://biocyc.org:1555/META/new-image?type=COMPOUND&object=$metaId{$_}\" target=\"_metacyc\">$_</A>" if $metaId{$_};
	   print "<TR><TD width=10%><DIV class=\"text10\">$link</DIV></TD><TD width=5%><DIV class=\"text10\">$meta{$_}</DIV></TD><TD width=5%><DIV class=\"text10\">$mw{$_}</DIV></TD><TD width=80% class=\"text10\"><div class=\"text10\">$ecs</div></TD></TR>\n"} sort keys %meta;
    }
    elsif ($sortByMW){
	map{
	    my $link = $_;
	    map {$_ = "<A HREF=\"http://escalante.lbl.gov/cgi-bin/fetchEC.cgi?ec=$_&taxId=$taxId\" target=\"_ec\">$_</A>";} @{ $meta2ec{$link} };
	    my $ecs = join ", ", @{$meta2ec{$_}};
	    print "<TR><TD width=10%><DIV class=\"text10\">$link</DIV></TD><TD width=5%><DIV class=\"text10\">$meta{$_}</DIV></TD><TD width=5%><DIV class=\"text10\">$mw{$_}</DIV></TD><TD width=80% class=\"text10\"><div class=\"text10\">$ecs</div></TD></TR>\n"} sort {$mw{$b}<=>$mw{$a}} keys %mw;
    }
    else{

	map{
	    my $link = $_;
	    map {$_ = "<A HREF=\"http://escalante.lbl.gov/cgi-bin/fetchEC.cgi?ec=$_&taxId=$taxId\" target=\"_ec\">$_</A>";} @{ $meta2ec{$link} };
	    my $ecs = join ", ", @{$meta2ec{$_}};
	    $link = "<A HREF=\"http://biocyc.org:1555/META/new-image?type=COMPOUND&object=$metaId{$_}\" target=\"_metacyc\">$_</A>" if $metaId{$_};
	    print "<TR><TD width=10%><DIV class=\"text10\">$link</DIV></TD><TD width=5%><DIV class=\"text10\">$meta{$_}</DIV></TD><TD width=5%><DIV class=\"text10\">$mw{$_}</DIV></TD><TD width=80% class=\"text10\"><div class=\"text10\">$ecs</div></TD></TR>\n"} sort{$meta{$b} <=> $meta{$a}} keys %meta;
    }
    
    
#print $out;
    print qq{</TABLE></DIV></BODY></HTML>};
}
    disconnect();

exit;

sub fetchBioCyc(\%\%){
    my ($biocycR, $metaIdR) = @_;
    my $dbh = SQL::connect("lut", "BioCyc");

    #using my own SQL.pm module to connect to BioCyc
    #SQL::query() returns a list of tab delimited results

    #load RxnPlayer info into %$biocycR

    #this query basically gets all reactants and products if key player "EC-NUMBER" exists in that rxn
    my $sql = qq{
	SELECT Reaction.name, A.playerId, B.player, B.playerId, Compound.name, Compound.mw, Protein.name, Protein.mw
	    FROM RxnPlayer A, RxnPlayer B, Reaction
	    LEFT JOIN Compound
	    ON Compound.compoundId=B.playerId
	    LEFT JOIN Protein
	    ON Protein.proteinId=B.playerId
	    WHERE A.player = "EC-NUMBER"
	    AND Reaction.rxnId=A.rxnId
	    AND  A.rxnId=B.rxnId
	    AND (B.player="LEFT" OR B.player="RIGHT")
	   
    };
    my $dataR = SQL::query($sql);
    foreach (@$dataR){
	s/\|//g;
	my ($rxnId, $ec, $meta, $metaId, $metaName, $metaMW, $protName, $protMW) = split /\t/;
	
	
        #print join "\t", $metaId, $metaName, $metaMW, $protName, $protMW."\n";
	if ($metaName){
	    #$metaMW = "na" if $metaMW==0;
	    $metaIdR->{$metaName} = $metaId;
	    push @{ $biocycR->{$ec}{$rxnId}{$meta} }, $metaName."\t".$metaMW;
	}
	elsif ($protName){
	    #$protMW = "na" if $protMW==0;
	    $metaIdR->{$protName} = $metaId;
	    push @{ $biocycR->{$ec}{$rxnId}{$meta} }, $protName."\t".$protMW;
	}
	else{
	    #$metaIdR->{$metaId} = $metaId;
	    push @{ $biocycR->{$ec}{$rxnId}{$meta} }, $metaId."\t".0;
	}

    }
    $dbh->disconnect();
    return;
}
