#!/usr/local/bin/perl

use lib ("./");
use strict;
use sql_engine;
use SNP_util;
use Data::Dumper;

my $OLD_STATEMENTS = statements->new('snptest_old');
$OLD_STATEMENTS->default_sql_lib_path('./');
$OLD_STATEMENTS->load_sql_lib(["cdna.sql_lib::get_libs"]);


my $NEW_STATEMENTS = statements->new('snptest');
$NEW_STATEMENTS->default_sql_lib_path('./');
$NEW_STATEMENTS->load_sql_lib(["cdna.sql_lib::all",
			       "ExoSeq.sql_lib::next_session",
			       "ExoSeq.sql_lib::next_dnaplate",
			       "ExoSeq.sql_lib::next_dnawell",
			       "ExoSeq.sql_lib::insert_dnawell",
			       "ExoSeq.sql_lib::insert_dnaplate",
			       "ExoSeq.sql_lib::insert_dnaplatestatus",
			       ]);


my $libs = $OLD_STATEMENTS->get('cdna.sql_lib::get_libs', []);

my %pools;

my ($lib_id, $sp_id);
my $i = 0;
my $water_id;

eval {

    $STATEMENTS->do('cdna.sql_lib::insert_dna_well_contents_dict', [2,
								    'SNP',
								    'cdna_superpool_tube',
								    'id_superpooltube',
								    'SNPOWNER'
								    ]);

    while ($libs->[$i]->[0] ne undef ) {
###	print $libs->[$i]->[0], $libs->[$i]->[1], $libs->[$i]->[2], $libs->[$i]->[3], $libs->[$i]->[4], $libs->[$i]->[5], "\n";

	my $lib_id = $libs->[$i]->[0];
	my $lib_name = $libs->[$i]->[1] . "_lib_v";

	$NEW_STATEMENTS->do('cdna.sql_lib::insert_lib', [$lib_id, 
							  $lib_name, 
							  $libs->[$i]->[2], 
							  $libs->[$i]->[3], 
							  $libs->[$i]->[4], 
							  $libs->[$i]->[5]  ]);
	    

	$NEW_STATEMENTS->do('cdna.sql_lib::insert_lib_tube', [$lib_id, 
							      $lib_id,
							      69,
							      '',
							      1,
							      ]); 


	foreach my $j (1..5) {
	    #### five superpools per library, A..E
	    $sp_id = $j + ($i*5);   ## superpool id

	    my $sp_name = $libs->[$i]->[1] . "_lib_v_SP" . chr($j+64);  ## superpool name
	    $pools{$sp_name} = $sp_id;

	    $NEW_STATEMENTS->do('cdna.sql_lib::insert_cdna_superpool_tube', [$sp_id,
									     $sp_name,
									     69 ]);

	    foreach my $k (1..5) {
		my $pool_num = $k + (($j-1)*5);
		my $p_id = $pool_num + ($i*25);   ## pool id

		my $pool_name = $libs->[$i]->[1] . "_lib_v" . $pool_num;
		print STDERR "$lib_name, $sp_name, $p_id, $pool_name\n";

		print STDERR "$p_id, $pool_name, $lib_id\n";
		$NEW_STATEMENTS->do('cdna.sql_lib::insert_cdna_pool_tube', [$p_id,
									    $pool_name,
									    $lib_id,
									    69,
									    '' 
									    ]);

		$NEW_STATEMENTS->do('cdna.sql_lib::insert_superpool_pool', [$p_id,
									    $sp_id,
									    ]);
	    }
	    ### only three libs superpools (and 15 pools) for 'P' library
	    if ( $lib_name eq 'P_lib_v' && $j eq 3 ) {
		last;
	    }
	}
	$i++;
    }

    $NEW_STATEMENTS->do('cdna.sql_lib::insert_cdna_superpool_tube', [$sp_id+1,
								     '+ve',
								     69 ]);
    
    $pools{'+ve'} = $sp_id+1;

    $NEW_STATEMENTS->do('cdna.sql_lib::insert_cdna_superpool_tube', [$sp_id+2,
								     '-ve',
								     69 ]);

    $pools{'-ve'} = $sp_id+2;

    $NEW_STATEMENTS->do('cdna.sql_lib::insert_cdna_superpool_tube', [$sp_id+3,
								     'WATER',
								     69 ]);

    $pools{'WATER'} = $sp_id+3;

    $water_id = $sp_id+3;

};
if ($@) {
    $NEW_STATEMENTS->{DBH}->rollback();
    print "database error: ", $@, "\n";
    exit;
}
else {
    $NEW_STATEMENTS->{DBH}->rollback();
    exit;
} 

END {
    if ($OLD_STATEMENTS){
	$OLD_STATEMENTS->end;
    }

    if ($NEW_STATEMENTS) {
	$NEW_STATEMENTS->end;
    }
}

eval {

    my %map = reverse ( SNP_util::get_map_into_hash('snptest', 96) );
    print Dumper(\%map);


    my %primary = ('AK'   => [qw(a1 a2 b1 b2 c1)],
		   'FLU'  => [qw(c2 d1 d2 e1 e2)],
		   'AH'   => [qw(f1 f2 g1 g2 h1)],
		   'AB'   => [qw(a3 a4 b3 b4 c3)],
		   'HeLa' => [qw(c4 d3 d4 e3 e4)],
		   'T'    => [qw(f3 f4 g3 g4 h3)],
		   'U'    => [qw(a5 a6 b5 b6 c5)],
		   'HPB'  => [qw(c6 d5 d6 e5 e6)],
		   'SK'   => [qw(f5 f6 g5 g6 h5)],   
		   '+ve'  => [qw(h2 h6)],
		   '-ve'  => [qw(h4)],
		);


    my %secondary = ('HSI'   => [qw(a1 a2 b1 b2 c1)],
		     'HL'    => [qw(c2 d1 d2 e1 e2)],
		     'FL'    => [qw(f1 f2 g1 g2 h1)],
		     'FB'    => [qw(a3 a4 b3 b4 c3)],
		     'ALU'   => [qw(c4 d3 d4 e3 e4)],
		     'H'     => [qw(f3 f4 g3 g4 h3)],
		     'Dau'   => [qw(a5 a6 b5 b6 c5)],
		     'YT'    => [qw(c6 d5 d6 e5 e6)],
		     'BM'    => [qw(f5 f6 g5 g6 h5)],   
		     '+ve'   => [qw(h2 h6)],
		     '-ve'   => [qw(h4)],
		     );

    my %tertiary = ('DX3'    => [qw(a1 a2 b1 b2 c1)],
		    'NK'     => [qw(c2 d1 d2 e1 e2)],
		    'Uact'   => [qw(f1 f2 g1 g2 h1)],
		    'P'      => [qw(a3 a4 b3)],
		    '+ve'    => [qw(h2)],
		    '-ve'    => [qw(b4)]
		     );
    

    print Dumper(\%primary);

    my $ses = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_session');
    my $session = $ses->[0]->[0];


    my $res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnaplate');
    my $plate_id = $res->[0]->[0];
    
    print "plate: ", $plate_id, " session ", $session, "\n";
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplate', [$plate_id, 96, 69, 'Primary']);
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplatestatus', [$plate_id, 
								  1,         # status
								  1,         # is_current
								  21842,     # id_role
								  $session, 
								  "gene_id"  
								  ]);
	
    
    foreach my $lib_name (keys %primary) {
	my $pos = $primary{$lib_name};
	my $name;
	
	foreach my $j (0..4) {
	    if ( $lib_name eq '+ve' || $lib_name eq '-ve' ) {
		$name = $lib_name;
	    }
	    else {
		$name = $lib_name . " - " . chr($j+65);   ## build superpool names
	    }
	    my $id = $pools{$name};
	    
	    if ( $pos->[$j] ne undef ) {
		my $id_map = $map{$pos->[$j]};
		print $name, " ", $id, " ", $pos->[$j], " ", $id_map, "\n";
	    
		$res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnawell');
		my $well_id = $res->[0]->[0];
		
		$NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnawell', [$well_id,
								       $plate_id,
								       $id_map,
								       $id,
								       2 ]);
	    }
	}
    }


    my $res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnaplate');
    my $plate_id = $res->[0]->[0];
    
    print "plate: ", $plate_id, " session ", $session, "\n";
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplate', [$plate_id, 96, 69, 'Secondary']);
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplatestatus', [$plate_id, 
								  1,         # status
								  1,         # is_current
								  21842,     # id_role
								  $session, 
								  "gene_id"  
								  ]);
	
    
    foreach my $lib_name (keys %secondary) {
	my $pos = $secondary{$lib_name};
	my $name;
	
	foreach my $j (0..4) {
	    if ( $lib_name eq '+ve' || $lib_name eq '-ve' ) {
		$name = $lib_name;
	    }
	    else {
		$name = $lib_name . " - " . chr($j+65);   ## build superpool names
	    }
	    my $id = $pools{$name};
	    
	    if ( $pos->[$j] ne undef ) {
		my $id_map = $map{$pos->[$j]};
		print $name, " ", $id, " ", $pos->[$j], " ", $id_map, "\n";
	    
		$res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnawell');
		my $well_id = $res->[0]->[0];
		
		$NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnawell', [$well_id,
								       $plate_id,
								       $id_map,
								       $id,
								       2 ]);
	    }
	}
    }




    my $res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnaplate');
    my $plate_id = $res->[0]->[0];
    
    print "plate: ", $plate_id, " session ", $session, "\n";
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplate', [$plate_id, 96, 69, 'Tertiary']);
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplatestatus', [$plate_id, 
								  1,         # status
								  1,         # is_current
								  21842,     # id_role
								  $session, 
								  "gene_id"  
								  ]);
	
    
    foreach my $lib_name (keys %tertiary) {
	my $pos = $tertiary{$lib_name};
	my $name;
	
	foreach my $j (0..4) {
	    if ( $lib_name eq '+ve' || $lib_name eq '-ve' ) {
		$name = $lib_name;
	    }
	    else {
		$name = $lib_name . " - " . chr($j+65);   ## build superpool names
	    }
	    my $id = $pools{$name};
	    
	    if ( $pos->[$j] ne undef ) {
		my $id_map = $map{$pos->[$j]};
		print $name, " ", $id, " ", $pos->[$j], " ", $id_map, "\n";
	    
		$res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnawell');
		my $well_id = $res->[0]->[0];
		
		$NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnawell', [$well_id,
								       $plate_id,
								       $id_map,
								       $id,
								       2 ]);
	    }
	}
    }

    ### insert a dummy dna plate to contain vectorette/link pcr WATER control

    my $res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnaplate');
    my $plate_id = $res->[0]->[0];


    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplate', [$plate_id, 96, 69, 'WATER']);
    
    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnaplatestatus', [$plate_id, 
								  1,         # status
								  1,         # is_current
								  21842,     # id_role
								  $session, 
								  "gene_id"  
								  ]);

    $res = $NEW_STATEMENTS->get('ExoSeq.sql_lib::next_dnawell');
    my $well_id = $res->[0]->[0];
    my $id_map = $map{a1};

    $NEW_STATEMENTS->do('ExoSeq.sql_lib::insert_dnawell', [$well_id,
							   $plate_id,
							   $id_map,
							   $water_id,
							   2 ]);
};
if ($@) {
    $NEW_STATEMENTS->{DBH}->rollback();
    print "database error: ", $@, "\n";
}
else {
    $NEW_STATEMENTS->{DBH}->rollback();
} 

#END {
#    if ($OLD_STATEMENTS){
#	$OLD_STATEMENTS->end;
#    }
#
#    if ($NEW_STATEMENTS) {
#	$NEW_STATEMENTS->end;
#    }
#}












