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

#person in charge of this script
#this should be the person currently in charge of scripts for the FGN project
my $script_maintainer='Teri Solow <tms45@cornell.edu>';

#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 (/(^|\W)-/, (join ' ', @ARGV));

my %args=();

foreach (@arg_pairs){

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

my $in_file=$args{'i'};
my $load_date=$args{'d'};

$in_file or die "You must specify a .accs confirmation file using the -i flag.\n";
$load_date or die "Please specify a date using the -d flag and YYYY-MM-DD format\n";

#create defaults
my $project = 'fgn';
my $script_file=__FILE__;


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;


my %confirmed_ids=();

#open file and load the identifiers

open FILEIN, "$in_file";

while (<FILEIN>){

    /^\s*[0-9]+\s/ or next;

    my ($dbest_id, $clone_id, $genbank_accn)=split /\t/;

    $clone_id =~ s/\s//g;

    push @{$confirmed_ids{$clone_id}}, $dbest_id;
    push @{$confirmed_ids{$clone_id}}, $genbank_accn;
}

#foreach (keys %confirmed_ids){
#    print "$_\t$confirmed_ids{$_}[0]\t$confirmed_ids{$_}[1]\n";
#}


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



foreach (keys %confirmed_ids){

#get the sequence id based on clone name
    my $seq_id;

    $stm = "select local_db_id from other_identifier where external_id_type='1' and external_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(\$seq_id);
    unless ($sth->fetch){
	print "$_ not found\n";
	next;
    }

#insert into genbank_submission
    $stm = "update genbank_submission set confirmed='1', date_confirmed='$load_date' where seq_id='$seq_id'";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    

#insert the db_est identifier
    $stm = "insert into other_identifier (local_db_id, external_id, external_id_type) values ('$seq_id', '$confirmed_ids{$_}[0]', '2')";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";
    

#insert the genbank identifier
    $stm = "insert into other_identifier (local_db_id, external_id, external_id_type) values ('$seq_id', '$confirmed_ids{$_}[1]', '3')";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";


}




#close the database link
db_link::disconnect_db($dbh);

runtime::runtime_print($start_time, "genbank confirmation load");