#!/usr/bin/env perl 
#use strict;
#use warnings;


use DBI; 
use constant DB_SOCKET => '/var/run/mysqld/mysqld.sock'; # Socket for mysql

use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );
use PS::IPP::Config 1.0.1 qw( :standard );

my $ipprc =  PS::IPP::Config->new(); # IPP Configuration
my $siteConfig = $ipprc->{_siteConfig};

my $filter, $dbname, $start, $end, $number, $help;
$number = 10;

GetOptions(
           'start|s=s'    => \$start,
           'end|e=s'      => \$end,
           'number|n=s'   => \$number,
           'dbname|d=s'   => \$dbname,
           'filter|f=s'    => \$filter,
           'help|h'     => \$help,
) or pod2usage( 0 );

if ($#ARGV != 1) {$help=1;}

if ($help) {
    print "gpc1gcirc.pl ra dec [-s date] [-e date] [-n number] [-filter filter] [-dbname db]\n";
    print "\n\nfinds nearest raw images for a given ra and dec\n\n";
    print "-s date : find exposures since date\n";
    print "-e date : fine exposures before date\n";
    print "-f filter : search  by filter\n";
    print "-n number : limit results to this number (default 10)\n";
    print "-d dbname : use this db (default gpc1)\n";
    print "-h : print help\n\n";

    exit 0;
}







my $where = '';
if ($filter) { 
    $where .= " AND filter = '$filter' ";
 }

if ($start) { 
    $where .= " AND dateobs > '$start%' "; 
 }

if ($end) { 
    $where .= " AND dateobs < '$end%' ";
 }

$ra = shift;
$dec = shift;




pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;
#pod2usage( -msg => "Required options: --start",
#           -exitval => 3)
#    unless defined $startDate;






if (!$dbname) {
    my $dbname = "gpc1";
}

my $db  = getDBHandle($ipprc, $dbname);


#$ra = shift;
#$dec = shift;

# -date order
# -n
# -f 
# convert from sexagesimal to decimal degrees if coordinates contain ':'
if ($ra =~ /:/) {
    my ($h, $m, $s) = split /:/, $ra;

    die "invalid ra: $ra\n" if !$h;

    my $sign = 1;
    if ($h < 0) {
        $sign = -1;
        $h = - $h;
    }
        
    $h += ($m / 60) if $m;
$h += ($s / 3600) if $s;

$ra = $h * 15 * $sign;
}

$ra = $ra*3.1415/180.0;
$dec = $dec*3.1415/180.0;


$dbname = 'gpc1';
$dbuser = 'ipp';
$dbpass = 'ipp';
$dbserver = 'ippdb01';

#my $db = DBI->connect(
#                      "DBI:mysql:database=${dbname};host=${dbserver};mysql_socket=" .
#    DB_SOCKET(),${dbuser},${dbpass}, 
#{
#    RaiseError => 1, AutoCommit => 1}
#    ) or die "Unable to connect to database $DBI::errstr\n";



my $qr = "select 180.0/3.1415*2.0*asin(sqrt( sin((decl-$dec)/2.0)*sin((decl-$dec)/2.0) + cos($dec)*cos(decl)*sin( (ra-$ra)/2.0)*sin((ra-$ra)/2.0) )) as dis , ra*180.0/3.1415, decl*180.0/3.1415, exp_name, filter, dateobs, comment from rawExp where exp_type = 'OBJECT' and (180.0/3.1415*2.0*asin(sqrt( sin((decl-$dec)/2.0)*sin((decl-$dec)/2.0) + cos($dec)*cos(decl)*sin( (ra-$ra)/2.0)*sin((ra-$ra)/2.0) ))) < 10 and exp_name like 'o____g%o' $where order by dis   limit $number ;";

my $query = $db->selectall_arrayref( $qr ) ||
    die "Unable to execute SQL: $DBI::errstr\n";
print "dis\tra\tdec\texp_name\tfilter\tdate_obs         \tcomment\n";
#printf "%s\t%s\t%s\t%s\t%s\t%20s\t%s\n", "dis", "ra", "dec", "exp_name", "filter", "date_obs", "comment";
foreach  $item (@{$query}) {
    printf "%5.3f\t%7.3f\t%7.3f\t%s\t%s\t%20s\t%s\n",${$item}[0],${$item}[1],${$item}[2],${$item}[3],${$item}[4],${$item}[5],${$item}[6];
}


$db->disconnect();

exit 0;



sub getDBHandle {
    my $ipprc = shift;
    my $dbname = shift;
    if (!$dbname) {
        $dbname = metadataLookupStr($siteConfig, 'DBNAME');
        if (($dbname eq 'XXX') or ($dbname eq "NONE")) {
            $dbname = 'gpc1';
        }
    }
    my $dbuser   = metadataLookupStr($siteConfig, 'DBUSER');
    my $dbpass   = metadataLookupStr($siteConfig, 'DBPASSWORD');
    my $dbserver = metadataLookupStr($siteConfig, 'DBSERVER');
    exit ($PS_EXIT_CONFIG_ERROR) unless defined $dbserver and $dbname and $dbuser and $dbpass;


    die "database enviornment not set up" unless defined($dbserver) and defined($dbuser)
        and defined($dbpass) and defined($dbname);

    my $dsn = "DBI:mysql:host=$dbserver;database=$dbname";

    my $dbh = DBI->connect($dsn, $dbuser, $dbpass) or die "Cannot connect to server\n";

    return $dbh;
}
