#!/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='Teri Solow <tms45@cornell.edu>';

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

# stdout hot
$|++;

# get/parse command line arguments
@ARGV or die "Usage:\n\tpull_build -p <project> -b <unigene_build_id>\n";
my @arg_pairs = split (/\B-/, (join ' ', @ARGV));
my %args=();
foreach (@arg_pairs){
	$_ or next;
	my ($flag, $val)=split /\s+/;
	$args{$flag}=$val;
}
my $project=$args{'p'};
my $build_id=$args{'b'};
$project or die "Please specify a project (cgn, fgn, pgn) using the -p flag\n";
$build_id or die "Please specify a unigene_build_id using the -b flag\n";
my ($db, $usr) = @{projects::get_db_info($project)};
$db or die "No known database for project $project";
# print "user:$usr, db:$db\n";

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

my $start_time=time;
my $numrows;

# connect to database
my $dbh = db_link::connect_db($db, $usr) or die "couldn't open database link\n";
#my $dbh = DBI->connect("dbi:mysql:$project;host=localhost", "teri", "dexter", { RaiseError => 1}) || die "Can't connect to database: $DBI::errstr";
# my $dbh = db_link::connect_db($db, $usr) or die "couldn't open database link\n";

# no matter what, try to delete the row from the unigene_build table with
# this build_id - there should only be one row.  deleting this row is
# sufficient to make any unigenes still associated with this build unavailable
# on the website
print "Deleting build $build_id from unigene_build.... ";
$numrows = $dbh->do("delete from unigene_build where unigene_build_id='$build_id' limit 1;") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

# for each unigene_element_id, delete all rows containing it from the following
# tables:
#	unigene_singleton
print "Deleting build $build_id from singleton table.... ";
$numrows = $dbh->do("delete from unigene_singleton where unigene_build_id='$build_id';") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

#	unigene_assemnbly
print "Deleting build $build_id from assembly table.... ";
$numrows = $dbh->do("delete unigene_assembly from unigene_assembly,unigenes where unigenes.unigene_build_id='$build_id' and unigenes.unigene_element_id=unigene_assembly.unigene_assembly_id;") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

#	unigene_assembly_sequence_quality
print "Deleting build $build_id from sequence_quality table.... ";
$numrows = $dbh->do("delete unigene_assembly_sequence_quality from unigene_assembly_sequence_quality,unigenes where unigenes.unigene_build_id='$build_id' and unigenes.unigene_element_id=unigene_assembly_sequence_quality.unigene_assembly_id;") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t$numrows rows affected\n";

#	unigene_assembly_sequence
print "Deleting build $build_id from sequence table.... ";
$numrows = $dbh->do("delete unigene_assembly_sequence from unigene_assembly_sequence,unigenes where unigenes.unigene_build_id='$build_id' and unigenes.unigene_element_id=unigene_assembly_sequence.unigene_assembly_id;") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

#	unigene_assembly_component
print "Deleting build $build_id from component table.... ";
$numrows = $dbh->do("delete unigene_assembly_component from unigene_assembly_component,unigenes where unigenes.unigene_build_id='$build_id' and unigenes.unigene_element_id=unigene_assembly_component.unigene_assembly_id;") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

# if all of that is successful, delete each row from unigenes with specified
# unigene_element_id/build_id (should be the same, may want to double check
# this first....)
print "Deleting build $build_id from unigenes table.... ";
$numrows = $dbh->do("delete from unigenes where unigene_build_id='$build_id';") or die "SQL Error: ".$dbh->errstr."\n";
($numrows eq "0E0") and $numrows = "0";
print "\t\t$numrows rows affected\n";

# disconnect from database
$dbh->disconnect || warn DBI::errstr;
# db_link::disconnect_db($dbh);

runtime::runtime_print($start_time, "Pull build");
