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

#person in charge of this script
#this should be the person currently in charge of scripts for the SGN project
my $script_maintainer='Dan Ilut <dci1@cornell.edu>';

use lib '/soldb/website/perllib';

#local packages to use
use runtime;
use db_link;
use projects;


@ARGV or print "No input parameters, proceeding with default.\n";

my @arg_pairs = split (/\-/, (join ' ', @ARGV));

my %args=();

foreach (@arg_pairs){

    $_ or next;
    my ($flag, $val)=split /\s+/;
    $args{$flag}=$val;
}

my $in_file=$args{'i'};
my $project='fgp';

$in_file or die "Please specify a list of ESTs to check\n";
$project or die "Please specify a project (cgn, fgn, pgn) using the -p flag\n";

my ($db, $usr) = @{projects::get_db_info($project)};
$db or die "No known database for project $project";


#main body of script
#####################

my $start_time=time;

# try to open the database
my $dbh = db_link::connect_db($db, $usr) or die "couldn't open database link\n";
my ($stm, $sth, $rv, $rc);


open FILEIN, $in_file or die "Couldn't open $in_file for read\n";

while (<FILEIN>){

    chomp;

#get the identifier for the EST
    my $est_id;
    $stm = "select e.est_library_id from est_info as e, other_identifier as o where o.external_id='$_' and o.local_db_id=e.seq_id";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    $rc = $sth->bind_columns(\$lib_id);
    unless ($sth->fetch){
	print "No library identifier found for $_\n";
	next;
    }

#get the assembly id for each EST
    my $assembly_id;
    $stm = "select a.lib_assembly_id from lib_assembly_component as a, other_identifier as o, lib_assembly as l where o.external_id='$_' and o.external_id_type='1' and o.local_db_id=a.seq_id and a.lib_assembly_id=l.lib_assembly_id and l.est_library_id='$lib_id'";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    $rc = $sth->bind_columns(\$assembly_id);

    unless ($sth->fetch){
	print "No assembly id found for $_\n";
	next;
    }


#get all the ESTs from that assembly
    my $est_name;
    $stm = "select o.external_id from other_identifier as o, lib_assembly_component as l where l.lib_assembly_id='$assembly_id' and l.seq_id=o.local_db_id";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    $rc = $sth->bind_columns(\$est_name);

    while ($sth->fetch){
	print "$_\t$est_name\n";
    }

}



db_link::disconnect_db($dbh);



# runtime::runtime_print($start_time, "Pulling EST contig siblings");