#!/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 '/data/shared/pgn_data_processing/scripts/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 (/\B-/, (join ' ', @ARGV));

my %args=();

foreach (@arg_pairs){

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

my $plate_name=$args{'n'};
my $project=$args{'p'};
my $date=$args{'d'};

#$plate_name or die "Please specify a plate to pull using the -n flag\n";
$project or die "Please specify a project (cgn, fgn, pgn) using the -p flag\n";
($plate_name or $date) or die "Please specify a plate or a date\n";
($plate_name and $date) and die "Please select either a date or a plate name\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;

# 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);

my @seq_list;

#get the seq_id list
my $seq_id;

if ($plate_name){

#uniform plate name
    $plate_name =~ tr/A-Z/a-z/;
    $stm = "select local_db_id from other_identifier where external_id_type='1' and external_id like '$plate_name%'";
}
elsif($date){

#format date
    $date=~tr/\-//d;
    $date.='000000';

    $stm = "select seq_id from sequence_group where creation_date >= $date";
}


$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(\$seq_id);
while ($sth->fetch){

    push @seq_list, $seq_id;

}

print "Found " . int(@seq_list) . " sequences, removing them...\n";


#delete all related entries to these sequences

$stm = "delete from adaptor_location where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed adaptor sequences.\n";

$stm = "delete from annotation where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed annotation.\n";

#$stm = "delete from blast_result where seq_id=?";
#$sth = $dbh->prepare($stm) 
#    || die "Can't prepare statement: $DBI::errstr";
#foreach (@seq_list){
#    $rv = $sth->execute($_)
#	|| die "Can't execute statement: $DBI::errstr";
#}
#
#print "Removed blast results.\n ";

$stm = "delete from est_info where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

$stm = "delete from polya_trim where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed est info.\n";

$stm = "delete from quality_evaluation where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed quality evaluation info.\n";

$stm = "delete from quality_trim where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed quality trim info.\n";

$stm = "delete from raw_sequence where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed raw sequences.\n";

$stm = "delete from raw_sequence_quality where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed raw sequence quality.\n";

$stm = "delete from sequence_group where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed sequence group.\n";

$stm = "delete from tracefile_location where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed tracefile info.\n";

$stm = "delete from trimmed_sequence where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed trimmed sequence.\n";

$stm = "delete from trimmed_sequence_quality where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed trimmed sequence quality.\n";

$stm = "delete from vector_pieces where seq_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed vector pieces info.\n";

$stm = "delete from other_identifier where local_db_id=?";
$sth = $dbh->prepare($stm) 
    || die "Can't prepare statement: $DBI::errstr";
foreach (@seq_list){
    $rv = $sth->execute($_)
	|| die "Can't execute statement: $DBI::errstr";
}

print "Removed other identifier.\n";


db_link::disconnect_db($dbh);