#!/usr/bin/perl -w
use strict;
use DBI;

if (!$ARGV[0] || $ARGV[0] eq "help") {
	print <<EOF;

	Quick script to return a tab-delimited file containing one unigene
	per line (NCBI accession #s) and ESTs that make up each unigene.

	Usage: <host> <user> <password> <database> <unigene_build> <filename>

EOF
	exit -1;
}

my ($host, $user, $pass, $db, $build, $filename) = @ARGV;
my $n = 0;
my $dbh = DBI->connect("dbi:mysql:host=$host;database=$db",$user,$pass,
			{ RaiseError => 1 });

# hash that will store the results 
my %unigenes = ();

# get info from the singletons
my $qs = $dbh->prepare("select u.unigene_id, o.external_id from unigenes as u left join unigene_singleton as s on s.unigene_singleton_id = u.unigene_element_id left join other_identifier as o on o.local_db_id=s.seq_id where u.unigene_build_id='$build' and o.external_id_type='3' and u.unigene_element_type_id='2'");
$qs->execute();
while(my ($unigene_id, $est_id) = $qs->fetchrow_array()) {
	chomp $est_id;
	$unigenes{$unigene_id}{'unigene_id'} = $unigene_id;
	push @{ $unigenes{$unigene_id}{'est_ids'} }, $est_id;
}

# get info from the contigs
my $qc = $dbh->prepare("select u.unigene_id, o.external_id from unigenes as u left join unigene_assembly_component as c on c.unigene_assembly_id=u.unigene_element_id left join other_identifier as o on o.local_db_id=c.seq_id where u.unigene_build_id='$build' and o.external_id_type='3' and u.unigene_element_type_id='1'");
$qc->execute();
while(my ($unigene_id, $est_id) = $qc->fetchrow_array()) {
	chomp $est_id;
	$unigenes{$unigene_id}{'unigene_id'} = $unigene_id;
	push @{ $unigenes{$unigene_id}{'est_ids'} }, $est_id;
}

# generate the output file
open OUT, ">${filename}" or die "Can't open output file \"${filename}\": ($!)\n";
for my $unigene (sort keys %unigenes) {
	$n++;
	my $out = $unigenes{$unigene}{'unigene_id'};
	foreach my $est (@{ $unigenes{$unigene}{'est_ids'} }) {
		if ($est && $est ne "NULL") {
			$out .= "\t$est";
		}
	}
	$out .= "\n";
	print OUT $out;
}
close OUT;

print STDERR "$n unigenes\n";