#!/usr/bin/perl -I /home/beth/pgn_processing/scripts/perllib
use strict;
$|=1;
use db_link;
my $old_dbh = db_link::connect_db('pgn-20050810', 'koni') or die "couldn't open database link\n";
my $pgn_dbh = db_link::connect_db('pgn', 'koni') or die "couldn't open database link\n";
my $fgn_dbh = db_link::connect_db('fgn', 'koni') or die "couldn't open database link\n";

my $seqs_list = '/data/shared/pgn_data_processing/misc_data/build_57_seq_ids';
open (my $fh, $seqs_list) or die "can't open $seqs_list: $!\n";
while(<$fh>){
    my $seq_id = $_;

    foreach my $t qw (
		     adaptor_location 
		     polya_trim 
		     quality_trim 
		     raw_sequence 
		     raw_sequence_quality 
		     sequence_group 
		     tracefile_location 
		     trimmed_sequence 
		   trimmed_sequence_quality
		   vector_pieces
		     ){

	print "inserting into $t...";

	my $old_rows = $old_dbh->selectall_arrayref("select * from $t where seq_id=$seq_id");
	foreach my $row (@$old_rows){

	    $row->[0] = 0;
	    @$row = map { $pgn_dbh->quote($_) } @$row;

	    local $"=', '; # for stringifying array
	    print "insert into $t values (@$row)\n";
	    $pgn_dbh->do("insert into $t values (@$row)") or die "this isn't right on PGN: @$row\n";
	    $fgn_dbh->do("insert into $t values (@$row)") or die "this isn't right on FGN: @$row\n";;
	}

	print "done.\n";
    } #end foreach table

    # other_identifier is a bit different.
    
	my $other_rows = $old_dbh->selectall_arrayref("select * from other_identifier where local_db_id=$seq_id");
	foreach my $row (@$other_rows){

	    $row->[0] = 0;
	    @$row = map { $pgn_dbh->quote($_) } @$row;

	    local $"=', '; # for stringifying array
	    #print "insert into $t values (@$row)\n";
	    $pgn_dbh->do("insert into other_identifier values (@$row)") or die "this isn't right on PGN: @$row\n";
	    $fgn_dbh->do("insert into other_identifier values (@$row)") or die "this isn't right on FGN: @$row\n";;
	}


    # quality_evaluation
    
	my $other_rows = $old_dbh->selectall_arrayref("select qual_eval_id, q.seq_id, q.trimmed_seq_id, qual_criteria_id from trimmed_sequence inner join quality_evaluation as q using(trimmed_seq_id) where trimmed_sequence.seq_id=$seq_id");
	foreach my $row (@$other_rows){

	    $row->[0] = 0;
	    @$row = map { $pgn_dbh->quote($_) } @$row;

	    local $"=', '; # for stringifying array
	    print "insert into quality_evaluation values (@$row)\n";
	    $pgn_dbh->do("insert into quality_evaluation values (@$row)") or die "this isn't right on PGN: @$row\n";
	    $fgn_dbh->do("insert into quality_evaluation values (@$row)") or die "this isn't right on FGN: @$row\n";;
	}

}
close $fh;