#!/usr/bin/perl

use strict;
use DBI;
use Time::HiRes qw(time);
use Bio::DB::GFF::Util::Binning;

# this is the smallest bin (1 K)
use constant MINBIN    => 1000;
# this is the largest that any feature can be (1 Mb)
use constant MAXBIN    => 1_000_000;

my $doretrieve= 1; #dgg

@ARGV = 'queries.txt' unless @ARGV;
my $dbh = DBI->connect('dbi:mysql:test') or die "Couldn't connect";

while (<>) {
  chomp;
  my ($seq_id,$start,$end) = split /\s+/;
  my $time = time;
  my $bin_query = bin_query($dbh,$start,$end);
  my $query = <<END;
select feature_id,fstart,fend from binloc
   where  ($bin_query)
      and fseq_id = ?
      and fend   >= ?
      and fstart <= ?
END
  ;
  my $sth = $dbh->prepare($query) or die $dbh->errstr;
  $sth->execute($seq_id,$start,$end);
  my $rows = $sth->rows;
  if ($doretrieve) {
    while (my @row = $sth->fetchrow_array) {
      # print "> ",join("\t",@row),"\n";
      }
    }

  my $elapsed = time-$time;
  print "$seq_id:$start..$end\t$rows rows in ",sprintf("%5.4f seconds, %5.4f sec/row\n",$elapsed,$elapsed/$rows);
  $sth->finish;
}
$dbh->disconnect;

sub bin_query {
  my $dbh = shift;
  my ($start,$end) = @_;
  my ($query,@args);

  $start = 0               unless defined $start;
  $end  = MAXBIN          unless defined $end;

  my @bins;
  my $minbin = MINBIN;
  my $maxbin = MAXBIN;
  my $tier = $maxbin;
  while ($tier >= $minbin) {
    my ($tier_start,$tier_end) = (bin_bot($tier,$start),bin_top($tier,$end));
    if ($tier_start == $tier_end) {
      push @bins,"fbin=$tier_start";
    } else {
      push @bins,"fbin between $tier_start and $tier_end";
    }
    $tier /= 10;
  }
  $query = join("\n\t OR ",@bins);
  $query;
}