use DBI;

#
# Database stuff
#

 $dbh;       # database handler
 $db_name     = 'largaespada_NGS_insertion_test';
#my $db_username = 'insertion_larga';
#my $db_password = 'uwtc.Pzc92';
 $db_username = 'sarvera';
 $db_password = 'fn101bcc';

 $db_host     = '127.0.0.1;mysql_local_infile=1';
 $db_type     = 'mysql';
 $data_source = "DBI:$db_type:database=$db_name;host=$db_host";

#
# global values
#
# allows multiple active instances to be running in the db...
$proj = 'merge_science';
$library_percent ='0.0001';

$CIS_total_pvalue = '0.05';
$CIS_library_pvalue = '0.05';
$CIS_region_pvalue = '0.05';

$cocis_threshold ='0.001';

$dbh = DBI->connect($data_source, $db_username, $db_password,
{ RaiseError => 1, AutoCommit => 0 });

sub resolve_barcodes {
#2.1 map the barcodes to the sequences..must change (sequence, #) to length of barcode +1 based on barcode sequence length!.
#Currently set up for 6,10,12 base barcodes.
####for 6mer barcodes...
#$sth = $dbh->prepare("create table illumina_decoded_$proj select library, id, substring(sequence,7) as decoded_sequence from barcode_$proj,illumina_raw_$proj where sequence like concat(seq,'%')");
####for 10mer barcodes...
$sth = $dbh->prepare("create table illumina_decoded_$proj select library, id, substring(sequence,11) as decoded_sequence from barcode_$proj,illumina_raw_$proj where sequence like concat(seq,'%')");
#for 12mer barcodes...
#$sth = $dbh->prepare("create table illumina_decoded_$proj select library, id, substring(sequence,13) as decoded_sequence from barcode_$proj,illumina_raw_$proj where sequence like concat(seq,'%')");

$sth->execute;

  }

sub resolve_IRDR {
#2.3 
#different options available this section needs to be optomized for each dataset
#for lung 454 data
$sth = $dbh->prepare("create table illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,32) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like '_____TGTATGTAAACTTCCGACTTCAACTG%'");

$sth->execute;
$sth = $dbh->prepare("insert into illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,31) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like '_____TGTATGTAACTTCCGACTTCAACTG%'");



#$sth = $dbh->prepare("create table illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,31) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like 'TTAAGTGTATGTAACTTCCGACTTCAACTG%'");

#for illumina data
#$sth = $dbh->prepare("create table illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,27) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like 'TGTATGTAAACTTCCGACTTCAACTG%'");

#for illumina_2 data
#$sth = $dbh->prepare("create table illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,30) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like '___TGTATGTAAACTTCCGACTTCAACTG%'");

#for MULV virus
#$sth = $dbh->prepare("create table illumina_without_IRDR_$proj select library,id,substring(decoded_sequence,26) as insertion_sequence, 'good' as type from illumina_decoded_$proj where decoded_sequence like 'CCAAACCTACAGGTGGGGTCTTTCA%'");
$sth->execute;
}