#!/usr/bin/perl
use strict;
use warnings;
use List::Util qw/ sum min max /;
use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=junk.lite","","",
	{PrintError => 1}) or die "Can't connect";

my $data = "data.txt";
open F, '<', $data or die "Unable to open '$data' for reading: $!";

open OUT, '>', 'outfile.csv'
	or die "Unable to open 'outfile.csv' for writing: $!";

while(my $table = <F>) {
	next if $table =~ /Header/;
	chomp $table;
	
	my ($chr, undef, $start, $end) = split /\t/, $table;
	
	my $sql = <<SQL;
SELECT score
FROM $chr
WHERE pos >= ? AND pos <= ?
SQL

	my $sth = $dbh->prepare($sql);
	$sth->execute( $start, $end ) or die $DBI::errstr; ;
	my $set = $sth->fetchall_arrayref;
	my @scores = map $_->[0], @$set;
	next unless @scores;
	
	my $len      = @scores; # number of scores
	my $min      = min @scores;
	my $max      = max @scores;
	my $mean     = sum(@scores) / @scores;
	my $geo_mean = geo_mean(@scores);
	
	print OUT join("\t",
		$table, $mean, $geo_mean, $min, $max, $len), "\n";
}
close(F) or die "Unable to close '$data': $!";
close(OUT) or die "Unable to close 'outfile.csv' $!";

sub geo_mean {
	my $prod = 1;
	$prod *= $_ for @_;
	return $prod ** (1/ @_);	
}