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

my %args=();

foreach (@arg_pairs){

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

my $plate_name='cccs42w';
my $project=$args{'p'};

$project or die "Please specify a project (cgn, fgn, pgn) using the -p flag\n";


#uniform plate name
$plate_name =~ tr/A-Z/a-z/;


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

my %seq_list=();

#get the seq_id list for all but the first 2 plates of cccs42w
my ($seq_id, $clone_id);
$stm = "select local_db_id, external_id from other_identifier where external_id_type='1' and external_id like '$plate_name%'";
$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, \$clone_id);
while ($sth->fetch){
    $clone_id =~ /cccs42w[12][a-z]/ and next;
    $seq_list{$seq_id}=$clone_id;
}


print "found " . (keys %seq_list) . " sequences\n";
my %plate_count;
foreach $seq_id (keys %seq_list){
    my $plate = $seq_list{$seq_id};
    $plate =~ s/^[^c]*(cccs42w[0-9]+)[a-z].*$/$1/;
    $plate_count{$plate}++;
}
foreach (keys %plate_count){
    print "$_: $plate_count{$_} seq\n";
}
print "changing these plates to cccs46w\n";


#change 42w to 46w
#update the library information, external id, and trace location

foreach $seq_id (keys %seq_list){

#change clone name
    my $new_clone_id=$seq_list{$seq_id};
    $new_clone_id =~ s/cccs42w/cccs46w/;

    $stm = "update other_identifier set external_id='$new_clone_id' where external_id_type='1' and local_db_id='$seq_id'";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";



#change library association
    $stm = "update est_info set est_library_id='8' where seq_id='$seq_id' and est_library_id='4'";
    $sth = $dbh->prepare($stm) 
	|| die "Can't prepare statement: $DBI::errstr";
    $rv = $sth->execute
	|| die "Can't execute statement: $DBI::errstr";



#change tracefile name
    my $trace_name;
    $stm = "select trace_name from tracefile_location 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";
    $rc = $sth->bind_columns(\$trace_name);
    $sth->fetch;

    $trace_name =~ s/cccs42w/cccs46w/;

    $stm = "update tracefile_location set trace_name='$trace_name' 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";

}


db_link::disconnect_db($dbh);



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