#! /usr/bin/perl -w

# find_archives.pl
# Author: Matthew Whiting
# Date: January 2008
# ATNF, CSIRO

# A modified version of Albert's find_archives.pl that hopefully works 
# a bit more consistently for the different data types.

# Crawls through data archive directories (given as an input parameter),
# searches for all observation and/or calibration files, enquires about
# their pulsar parameters and inputs these details as well as their
# location into a mySQL database

use strict;
#use lib "/DATA/SITAR_1/whi550/VO/PulsarVO/SQL-Abstract-1.22/blib/lib";
use File::Find ();
use DBI qw(:sql_types);
use SQL::Abstract;
use POSIX;

#  foreach (@{ $DBI::EXPORT_TAGS{sql_types} }) {
#      printf "%s=%d\n", $_, &{"DBI::$_"};
#  }

# for the convenience of &wanted calls, including -eval statements:
use vars qw/*name *dir *prune @archive_dirs $archive_dir @files
    $vap_cmd $vap_out $params $num_vap_params $db_handle
    $filename $raj $decj $dm $period $bw $cnfg 
    $freq $inst $mjdint $mjdfrac $npol $nchan $nbin $nsub
    $rcvr $site $length $obsrvr $data_loc $data_type
    $file_size $MJD $raH $raM $raS $decD $decM $decS $rajd $decjd $decstring
    $pi $pion180 $NGP_RA $NGP_DEC $ASC_NODE $draR $decR 
    $sinb $sinl $cosl $gb $gl $gl_raw 
    $bmaj $bmin $bpa $hdrver $survey $nbeam
    $output $vap $archive_extensions $ext	
    $default
    @result_params $FILE_SIZE_INDEX $FB $DFB $WBC $CPSR2 $NULLFIELD
    $params_NAME    
    $params_PROJID  
    $params_RAJ     
    $params_DECJ    
    $params_FREQ    
    $params_BW      
    $params_LENGTH  
    $params_DATE    
    $params_TIME    
    $params_MJD
    $params_BMAJ    
    $params_BMIN    
    $params_BPA     
    $params_DM      
    $params_PERIOD  
    $params_NCHAN   
    $params_NPOL    
    $params_NBIN    
    $params_NSUB    
    $params_NBITS   
    $params_TSAMP   
    $params_NBEAM
    $params_CNFG    
    $params_INST    
    $params_RCVR    
    $params_HDRVER
    $params_TELESCOP
    $params_SITE    
    $params_OBSRVR  
    $params_FILENAME
    /;  # $machine
						
*name   = *File::Find::name;
*dir    = *File::Find::dir;
*prune  = *File::Find::prune;

# The data types
$FB = "FB";
$WBC = "WBC";
$DFB = "DFB";
$CPSR2 = "CPSR2";

# filename extensions
$archive_extensions = "rf cf cfb fb";

# How to write NULL fields to the database
#$NULLFIELD = undef;
$NULLFIELD = "NULL";

# Pulsar parameters (listed in "vap -H")
$params  = "name projid ra dec freq bw length stt_date stt_time mjd bmaj bmin bpa dm period nchan npol nbin nsub nbits tsamp nbeam beconfig backend rcvr hdrver telescop asite observer";

# The location of vap
$vap = "/pulsar/psr/linux/bin/vap -n";

# vap result parameter indices. Used to locate the resulting 
# parameter's location
# Note that we start at 1, since the vap output has the filename in the first position
$params_NAME    =  1;
$params_PROJID  =  2;
$params_RAJ     =  3;
$params_DECJ    =  4;
$params_FREQ    =  5;
$params_BW      =  6;
$params_LENGTH  =  7;
$params_DATE    =  8;
$params_TIME    =  9;
$params_MJD     = 10;
$params_BMAJ    = 11;
$params_BMIN    = 12;
$params_BPA     = 13;
$params_DM      = 14;
$params_PERIOD  = 15;
$params_NCHAN   = 16;
$params_NPOL    = 17;
$params_NBIN    = 18;
$params_NSUB    = 19;
$params_NBITS   = 20;
$params_TSAMP   = 21;
$params_NBEAM   = 22;
$params_CNFG    = 23;
$params_INST    = 24;
$params_RCVR    = 25;
$params_HDRVER  = 26;
$params_TELESCOP= 27;
$params_SITE    = 28;
$params_OBSRVR  = 29;
$params_FILENAME= 30;


#####################
# Begin main method
#
my @vap_params = split(/ /, $params);
$num_vap_params = scalar(@vap_params);

# The column number (starting from 0) of the file 
# size when calling ls -l
$FILE_SIZE_INDEX = 4;

# Use the default operation which is to firstly delete any
# stale records, then crawl through and insert new records
$default = 1;

if (scalar(@ARGV) == 0) {
	print 
"find_archives.pl: A crawler that populates a relational database\
 		with indexes to observational data along with their respective\
		cal files.\n\
 Usage: find_archives.pl [options] directory1 [directory2 ...]\
	If no options are provided, it will default to do both -d and -p\
 Options:\
 \t-d Delete stale records\
 \t-p Populate database with new records\n";
	
}

# Connect to the database
$db_handle = connectdb();

#########################################################
# Step 1. First remove any stale entries in the database
#         (remove file locations that no longer exist)
#########################################################

if ($ARGV[0] eq "-d") {
	deleteStaleRecords();
	$default = 0;
	shift @ARGV;
}


#########################################################
# Step 2. Crawl through each input directory and insert
#         any new archives

if ($ARGV[0] eq "-p") {
	shift @ARGV;
	@archive_dirs = @ARGV;

	foreach $archive_dir (@archive_dirs) {

		# Traverse desired filesystems
		File::Find::find({wanted => \&wanted}, $archive_dir);
	}
	$default = 0;
}

# Else do both
if ($default) {

	deleteStaleRecords();

	@archive_dirs = @ARGV;

	foreach $archive_dir (@archive_dirs) {
		
		# Check that the input directory is legit
		
		# Traverse desired filesystems
		File::Find::find({wanted => \&wanted}, $archive_dir);
	}

}
#if ($db_handle->disconnect()) {
#	print "Successfully Disconnected from database\n";
#}
#else {
#	print "Error: Failed to disconnect from the database\n";
#	exit;
#}

######################################################################

######################################################################
# Subroutine definitions
#
######################################################################

#######################################################

sub wanted {
  
	
	if (-d and /^[a-zA-Z]{0,1}[0-9]{4}[+-][0-9]{4}.*/s ) {

	    if ($File::Find::name !~ /.*S$/) { # ignore scrunch directories

		print "\n\nIn directory $_\n\n";
		print "\$File::Find::name = $File::Find::name\n";
		
		my $search_str;
		foreach $ext (split (/\s+/, $archive_extensions) ) {
			$search_str .= $File::Find::name . "/*.$ext "
		}

		my @all_files = glob($search_str);

		print "Number of files found = " . scalar(@all_files) . "\n";
		
		if (scalar(@all_files) == 0) {
		    # No data files means it's likely to be a CPSR2 directory

		    # Foreach observation
		    my @cpsr2_obs_dirs = glob($File::Find::name . "/????-??-??-??:??:??");
		    
		    print "Number of CPSR2 obs found = " . scalar(@cpsr2_obs_dirs) . "\n";
		
		    my $cpsr2_obs_dir;
		    foreach $cpsr2_obs_dir (@cpsr2_obs_dirs) {
			print "dir is $cpsr2_obs_dir\n";
			
			# Need to check if this entry already exists
			$cpsr2_obs_dir =~ s/\s//g;
			
			if (isDuplicate($cpsr2_obs_dir) == 1) {
			    print "$cpsr2_obs_dir is a duplicate entry. Skipping....\n\n";
			    next;
			}				
			
			print "$cpsr2_obs_dir NOT a duplicate. Extracting data...\n";
			find_data_cpsr2($cpsr2_obs_dir);			
			
		    }
		    
		}
		else{

		    my $file;
		    foreach $file (@all_files) {
			
			# Need to check if this entry already exists
			$file =~ s/\s//g;
			
			if (isDuplicate($file) == 1) {
			    print "$file is a duplicate entry. Skipping....\n\n";
			    next;
			}				
			
			print "$file NOT a duplicate. Inserting into database here ...\n";
			find_data($file);
			
		    }
		    
		}
		
	    }
	    
	}
    }
		


#######################################################

sub find_data {
    
# Usage: find_data(filename)
# Runs vap on the filename, and extracts parameters, then sends them to populate_database() to be written to the database
#
# NOTE: this is for non-CPSR2 data

    my $file = $_[0];

    my $vap_cmd = "$vap -c \"$params\" $file  | grep -v filename | grep -v -x \"\" |";
    print "Calling vap...$vap_cmd\n";
    open(VAP,$vap_cmd);
    while ($vap_out = <VAP>) {
	$vap_out =~ s/^\s+//g; #remove any leading space
	my @result_params = split(/\s+/, $vap_out);
	
	populate_database(@result_params,$file);
#	if (scalar(@result_params) == ($num_vap_params + 1)) {    # +1 because first column is filename
#	    print "@result_params\n";
#	    populate_observations($file);
#	}
#	else {
#	    print "Incorrect number of cols = ".scalar(@result_params).". Expected " . $num_vap_params+1 ."\n";
#	    print "@result_params\n";
#	}
    }
    print "\n";
 
}

#######################################################

sub find_data_cpsr2 {
    
# Usage: find_data_cpsr2(filename) where filename = ????-??-??-??:??:??
# Extracts parameters for the observation using vap, then sends them to populate_database() to be written to the database
#
# NOTE: this is only for CPSR2 data

    my $dir = $_[0];
    my $ext;
    my @result_params;
    my @tmp_params;
    my $prefix = "m n o";

    my @all_files_fb = glob($dir."/*.fb");
    my @all_files_cfb = glob($dir."/*.cfb");
    if ( scalar(@all_files_fb)!=0 ){
	$ext = ".fb";
    }
    else{
	$ext = ".cfb";
    }
    print "Searching in $dir for $ext files\n";

    my $pre;
    foreach $pre (split (/\s+/, ($prefix))) {

	my @all_files;
	my $search_str = $dir."/".$pre."*".$ext;
	print "$pre: Searching for files like: $search_str\n";
	@all_files = glob($search_str);
	print "Found ". scalar(@all_files) . "\n";

	if(scalar(@all_files) != 0) {
    
	    my $total_length=0;
	    my $file;
	    my $firstGoodOne = 0;
	    foreach $file (@all_files) { #loop over all files in directory to get total obs time.
		my @tmp_params;
		my $vap_cmd = "$vap -n -c \"$params\" $file  | grep -v filename | grep -v -x \"\" |";
		open(VAP,$vap_cmd);
		my $vap_out;
		while ($vap_out = <VAP>) {
		    $vap_out =~ s/^\s+//g; #remove any leading space
		    @tmp_params = split(/\s+/, $vap_out);
		}

#		print "filename = $file\ttmp_params[0] = $tmp_params[0]\tfirstGoodOne = $firstGoodOne\n";

		my @tmpdirs = split(/\//, $file);
		my $tmpfilename = $tmpdirs[$#tmpdirs];

		if((scalar(@tmp_params)>0) && ($tmpfilename eq $tmp_params[0])){

		    if($firstGoodOne == 0){
			# Keep most of the parameters for the first good file
			@result_params = @tmp_params;
			$firstGoodOne = 1;
		    }
		    $total_length += $tmp_params[$params_LENGTH];
		}
	    }

	    if($firstGoodOne != 0){
		print "total length = $total_length\n";
		$result_params[$params_LENGTH] = $total_length;
	    
		populate_database(@result_params,$dir);
	    }
	}    
    }

}

#######################################################

sub populate_database {
    
# Usage: populate_database(file, $result_params), where file has the full path to the file/directory in question,
#   and $result_params have been filled with the vap results
#
#  This subroutine calculates all necessary parameters (such as data type, decimal positions, beam, file size, ...)
#   and writes them to the database.

    my @results = @_;
    my $full_filename = $results[$#results];
#    @results = shift @results;
    print "full_filename = $full_filename\nRESULT_PARAMS (POP):\n@results\n";

    my @dirs = split(/\//, $full_filename);
    my $path = $dirs[0];
    my $i;
    for ($i=1; $i < $#dirs; $i++){
	$path .= "/".$dirs[$i];
    }
    my $filename = $dirs[$#dirs];
    print "path = $path, filename = $filename\n";
    
    if( $filename =~ /[0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}:[0-9]{2}:[0-9]{2}/ ) {
	# Case of CPSR2 data file
	$data_type = $CPSR2;	
	
	my @time_date = split('-', $filename);
	$results[$params_DATE] = $time_date[0]."-".$time_date[1]."-".$time_date[2];
	$results[$params_TIME] = $time_date[3];
	
	my $prefix = substr $results[0], 0, 1;
	print "vap filename = $results[0] and the prefix = $prefix\n";

	my $du = `du -sb $full_filename/$prefix*fb | awk 'BEGIN{sum=0}{sum+=\$1}END{print sum}'`;
	print "Running du: -> du -sb $full_filename/$prefix*fb | awk 'BEGIN{sum=0}{sum+=\$1}END{print sum}'\n";
	my @file_details = split(/\s+/, $du);
	$file_size = $file_details[0];
    }
    else{
	if ($filename =~ /^a.*\.rf/ || $filename =~ /^a.*\.cf/ || 
	    $filename =~ /^r.*\.rf/ || $filename =~ /^r.*\.cf/) {
	    $data_type = $DFB;
	}
	elsif ($filename =~ /^w.*\.rf/ || $filename =~ /^w.*\.cf/) {
	    $data_type = $WBC;
	}
	else {
	    $data_type = $NULLFIELD;
	}
	
	my $ls = `ls -l $full_filename`;
	my @file_details = split(/\s+/, $ls);
	$file_size = $file_details[$FILE_SIZE_INDEX];
    }

    print "RESULT_PARAMS (POP 2):\n@results\n";
    
    #### Calculate the RA and Dec in decimal degrees.

    ($raH,$raM,$raS) = split(':', $results[$params_RAJ]);
    $rajd = ($raH + $raM/60. + $raS/3600.) * 15.;
    
    ($decD,$decM,$decS) = split(':', $results[$params_DECJ]);
    $decjd = (abs($decD) + $decM/60. + $decS/3600.);
    my @decstring = split(/ */,$results[$params_DECJ]);
    if($decstring[0] eq '-'){
	$decjd = -1. * $decjd; 
    }

    ### Calculate the Galactic Longitude and latitude
    $pi=asin(1) * 2.;
    $pion180 = $pi/180.;
    $NGP_RA = 192.859508 * $pion180; # location of NGP
    $NGP_DEC= 27.128336 * $pion180;
    $ASC_NODE=32.932;
    
    $draR = $rajd*$pion180 - $NGP_RA;
    $decR = $decjd*$pion180;
    $sinb = cos($decR) * cos($NGP_DEC) * cos($draR) + sin($decR) * sin($NGP_DEC);
    $gb = asin($sinb); # this is the latitude, but in radians.
    
    $sinl = (sin($decR) * cos($NGP_DEC) - cos($decR) * cos($draR) * sin($NGP_DEC)) / cos($gb);
    $cosl = cos($decR) * sin($draR) / cos($gb);
    
    # Need to get the correct quadrant, as this isn't preserved by
    # atan, which returns angle between -90 and 90.
    $gl_raw = atan($sinl/$cosl);
    if($sinl > 0){
	if($cosl > 0 ){ $gl = $gl_raw; }
	else { $gl = $gl_raw + $pi; }
    }
    else {
	if($cosl > 0){ $gl = $gl_raw + 2.*$pi; }
	else{ $gl = $gl_raw + $pi; }
    }
    # Now put them into degrees.
    $gb = $gb / $pion180;
    $gl = ($gl / $pion180) + $ASC_NODE;
    
    print "rajd = $rajd, decjd=$decjd, gl=$gl, gb=$gb\n";


    # Beam information
    $bmaj = $results[$params_BMAJ];
    $bmin = $results[$params_BMIN];
    $bpa = $results[$params_BPA];
    if($bmaj eq "UNDEF" || $bmaj eq "*" || $bmaj == 0.){
	# could not get beam info from vap output. 
	# Need to calculate manually.
	if( $results[$params_FREQ] != 0.){
	    # if the frequency is zero, do not calculate these...
	    $bmaj = (1.2*(299792458./($results[$params_FREQ] * 1.e6))/64.) / $pion180;
	    $bmin = (1.2*(299792458./($results[$params_FREQ] * 1.e6))/64.) / $pion180;
	    $bpa = 0.;
	}
	else{
	    $bmaj = $NULLFIELD;
	    $bmin = $NULLFIELD;
	    $bpa = $NULLFIELD;
	}
    }
    
    if($results[$params_NBITS] eq "UNDEF" || $results[$params_NBITS] == 0){
	$results[$params_NBITS] = $NULLFIELD;
    }
    if($results[$params_TSAMP] eq "UNDEF" || $results[$params_TSAMP] == 0){
	$results[$params_TSAMP] = $NULLFIELD;
    }

    # Fix telescope -- always PARKES
    if($results[$params_TELESCOP] eq "unknown") {
	$results[$params_TELESCOP] = "PARKES";
    }

    # Dud things that aren't required for this set of data.
    $survey = $NULLFIELD;
    $nbeam = 1;
	
    # Clean up any NULL parameters
    for ($i = 0; $i <= $#results; $i++) {
	if ($results[$i] eq "N/A" || $results[$i] eq "UNDEF" || 
	    $results[$i] eq "INVALID" || 
	    $results[$i] eq "*error*" || $results[$i] eq "*") {

	    $results[$i] = $NULLFIELD;

	}
    }


    # print out the list of parameters 
    print "filename = $filename\n";
    print "src_name = " . $results[$params_NAME] . "\n";
    print "projid = " . $results[$params_PROJID] . "\n";
    print "raj = " . $results[$params_RAJ] . "\n";
    print "dec = " . $results[$params_DECJ] . "\n";
    print "data_type = $data_type\n";
    print "freq = " . $results[$params_FREQ] . "\n";
    print "bw = " . $results[$params_BW] . "\n";
    print "scanlen = " . $results[$params_LENGTH] . "\n";
    print "date = " . $results[$params_DATE] ."\n";
    print "ut = " . $results[$params_TIME] ."\n";
    print "MJD = " . $results[$params_MJD] ."\n";
    print "rajd = " . $rajd ."\n";
    print "decjd = " . $decjd ."\n";
    print "gl = " . $gl ."\n";
    print "gb = " . $gb ."\n";
    print "bmaj = " . $bmaj ."\n";
    print "bmin = " . $bmin ."\n";
    print "bpa = " . $bpa ."\n";
    print "dm = " . $results[$params_DM] . "\n";
    print "period = " . $results[$params_PERIOD] . "\n";
    print "nchan = " . $results[$params_NCHAN] . "\n";
    print "npol = " . $results[$params_NPOL] . "\n";
    print "nbin = " . $results[$params_NBIN] . "\n";
    print "nsub = " . $results[$params_NSUB] . "\n";
    print "tsamp = " . $results[$params_TSAMP] . "\n";
    print "nbits = " . $results[$params_NBITS] . "\n";
    print "nbeam = " . $nbeam . "\n";
    print "cnfg = " . $results[$params_CNFG] . "\n";
    print "inst = " . $results[$params_INST] . "\n";
    print "rcvr = " . $results[$params_RCVR] . "\n";
    print "hdrver = " . $results[$params_HDRVER] . "\n";
    print "survey = " . $survey . "\n";
    print "telescope = " . $results[$params_TELESCOP] . "\n";
    print "site = " . $results[$params_SITE] . "\n";
    print "obsrvr = " . $results[$params_OBSRVR] . "\n";
    print "data_loc = $path\n";
    print "file_size = $file_size bytes\n";

    # Clean up any NULL parameters ready for SQL import
    for ($i = 0; $i <= $#results; $i++) {
	if ($results[$i] eq $NULLFIELD) {
	    $results[$i] = undef;
	}
    }

######### Now write the data to the mySQL database

    my %data = ( filename => $filename,
		 src_name => $results[$params_NAME],
		 project_id => $results[$params_PROJID],
		 raj => $results[$params_RAJ],
		 decj => $results[$params_DECJ],
		 data_type => $data_type,
		 obsfreq => $results[$params_FREQ],
		 bw => $results[$params_BW],
		 scanlen => $results[$params_LENGTH],
		 date => $results[$params_DATE],
		 ut => $results[$params_TIME],
		 MJD => $results[$params_MJD],
		 rajd => $rajd,
		 decjd => $decjd,
		 gl => $gl,
		 gb => $gb,
		 BMAJ => $bmaj,
		 BMIN => $bmin,
		 BPA => $bpa,
		 dm => $results[$params_DM],
		 period => $results[$params_PERIOD],
		 nchan => $results[$params_NCHAN],
		 npol => $results[$params_NPOL],
		 nbin => $results[$params_NBIN],
		 nsub => $results[$params_NSUB],
		 tsamp => $results[$params_TSAMP],
		 nbits => $results[$params_NBITS],
		 nbeam => $nbeam,
		 cnfg => $results[$params_CNFG],
		 inst => $results[$params_INST],
		 rcvr => $results[$params_RCVR],
		 hdrver => $results[$params_HDRVER],
		 survey => $survey,
		 telescope => $results[$params_TELESCOP],
		 site => $results[$params_SITE],
		 obsrvr => $results[$params_OBSRVR],
		 data_loc => $path,
		 file_size_bytes => $file_size
		 );
		
    my $sql = SQL::Abstract->new;
    my $tableName;
    if ($path =~ /\_R/ ) {
	$tableName = 'cals';
    }
    else {
	$tableName = 'observations';
    }    
    my($stmt, @bind) = $sql->insert($tableName, \%data);

    my $sth = $db_handle->prepare($stmt);
    $sth->execute(@bind);




#    if ($path =~ /\_R/ ) {
#	print "Inserting cal file $filename\n";
#	
#	$sql = qq{ INSERT INTO cals 
#		       (filename, src_name, project_id,
#			raj, decj, data_type, obsfreq, bw,
#			scanlen, date, ut, MJD, 
#			rajd, decjd, gl, gb, BMAJ, BMIN, BPA,
#			dm, period, nchan, npol, nbin, nsub, 
#			tsamp, nbits, nbeam,
#			cnfg, inst, rcvr, hdrver, survey,
#			telescope, site, obsrvr, 
#			data_loc, file_size_bytes
#			) 
#		       
#		       VALUES 
#		       ( ?, ?, ?
#			 ?, ?, ?, CAST(? AS DECIMAL), CAST(? AS DECIMAL),
#			 CAST(? AS DECIMAL), ?, ?, CAST(? AS DECIMAL), 
#			 CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL),
#			 CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
#			 CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
#			 ?, ?, ?, ?, ?, 
#			 ?, ?, ?, 
#			 ?, CAST(? AS UNSIGNED) 
#			 ) 
##		       ( ?, ?, ?
##			 ?, ?, ?, ?, ?,
##			 ?, ?, ?, ?, 
##			 ?, ?, ?, ?, ?, ?, ?, 
##			 ?, ?, ?, ?, ?, ?, 
##			 ?, ?, ?, 
##			 ?, ?, ?, ?, ?, 
##			 ?, ?, ?, 
##			 ?, ?, 
##			 ) 
#		   };
#    }
#    else {
#	
#	print "Inserting obs file $filename\n";
#	
#	$sql = qq{ INSERT INTO observations 
#		       (filename, src_name, project_id,
#			raj, decj, data_type, obsfreq, bw,
#			scanlen, date, ut, MJD, 
#			rajd, decjd, gl, gb, BMAJ, BMIN, BPA,
#			dm, period, nchan, npol, nbin, nsub, 
#			tsamp, nbits, nbeam,
#			cnfg, inst, rcvr, hdrver, survey,
#			telescope, site, obsrvr, 
#			data_loc, file_size_bytes
#			) 
#		       
#		       VALUES 
#		       ( ?, ?, ?
#			 ?, ?, ?, CAST(? AS DECIMAL), CAST(? AS DECIMAL),
#			 CAST(? AS DECIMAL), ?, ?, CAST(? AS DECIMAL), 
#			 CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS DECIMAL),
#			 CAST(? AS DECIMAL), CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
#			 CAST(? AS DECIMAL), CAST(? AS UNSIGNED), CAST(? AS UNSIGNED),
#			 ?, ?, ?, ?, ?, 
#			 ?, ?, ?, 
#			 ?, CAST(? AS UNSIGNED) 
#			 ) 
##		       ( ?, ?, ?
##			 ?, ?, ?, ?, ?,
##			 ?, ?, ?, ?, 
##			 ?, ?, ?, ?, ?, ?, ?, 
##			 ?, ?, ?, ?, ?, ?, 
##			 ?, ?, ?, 
##			 ?, ?, ?, ?, ?, 
##			 ?, ?, ?, 
##			 ?, ?, 
##			 ) 
#		   };
#    }
#	my $sth = $db_handle->prepare( $sql );
##DEBUGGING	
##    my $sth;
#    
#    #for( @records ) {
#    eval {
#	
##	$sth->bind_param( 1,  $filename);
##	$sth->bind_param( 2,  $results[$params_NAME]);
##	$sth->bind_param( 3,  $results[$params_PROJID]);
##	$sth->bind_param( 4,  $results[$params_RAJ]);
##	$sth->bind_param( 5,  $results[$params_DECJ]);
##	$sth->bind_param( 6,  $data_type);
##	$sth->bind_param( 7,  $results[$params_FREQ]);
##	$sth->bind_param( 8,  $results[$params_BW]);
##	$sth->bind_param( 9,  $results[$params_LENGTH]);
##	$sth->bind_param( 10, $results[$params_DATE]);
##	$sth->bind_param( 11, $results[$params_TIME]);
##	$sth->bind_param( 12, $results[$params_MJD]);
##	$sth->bind_param( 13, $rajd);
##	$sth->bind_param( 14, $decjd);
##	$sth->bind_param( 15, $gl);
##	$sth->bind_param( 16, $gb);
##	$sth->bind_param( 17, $bmaj);
##	$sth->bind_param( 18, $bmin);
##	$sth->bind_param( 19, $bpa);
##	$sth->bind_param( 20, $results[$params_DM]);
##	$sth->bind_param( 21, $results[$params_PERIOD]);
##	$sth->bind_param( 22, $results[$params_NCHAN]);
##	$sth->bind_param( 23, $results[$params_NPOL]);
##	$sth->bind_param( 24, $results[$params_NBIN]);
##	$sth->bind_param( 25, $results[$params_NSUB]);
##	$sth->bind_param( 26, $results[$params_TSAMP]);
##	$sth->bind_param( 27, $results[$params_NBITS]);
##	$sth->bind_param( 28, $nbeam);
##	$sth->bind_param( 29, $results[$params_CNFG]);
##	$sth->bind_param( 30, $results[$params_INST]);
##	$sth->bind_param( 31, $results[$params_RCVR]);
##	$sth->bind_param( 32, $results[$params_HDRVER]);
##	$sth->bind_param( 33, $survey);
##	$sth->bind_param( 34, $results[$params_TELESCOP]);
##	$sth->bind_param( 35, $results[$params_SITE]);
##	$sth->bind_param( 36, $results[$params_OBSRVR]);
##	$sth->bind_param( 37, $path);
##	$sth->bind_param( 38, $file_size);
#
##	$sth->bind_param( 1,  $filename,                  {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 2,  $results[$params_NAME],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 3,  $results[$params_PROJID],   {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 4,  $results[$params_RAJ],      {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 5,  $results[$params_DECJ],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 6,  $data_type,                 {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 7,  $results[$params_FREQ],     {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 8,  $results[$params_BW],       {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 9,  $results[$params_LENGTH],   {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 10, $results[$params_DATE],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 11, $results[$params_TIME],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 12, $results[$params_MJD],      {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 13, $rajd,                      {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 14, $decjd,                     {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 15, $gl,                        {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 16, $gb,                        {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 17, $bmaj,                      {TYPE => SQL_FLOAT } );
##	$sth->bind_param( 18, $bmin,                      {TYPE => SQL_FLOAT } );
##	$sth->bind_param( 19, $bpa,                       {TYPE => SQL_FLOAT } );
##	$sth->bind_param( 20, $results[$params_DM],       {TYPE => SQL_DOUBLE } );
##	$sth->bind_param( 21, $results[$params_PERIOD],   {TYPE => SQL_DOUBLE } );    
##	$sth->bind_param( 22, $results[$params_NCHAN],    {TYPE => SQL_INTEGER } );
##	$sth->bind_param( 23, $results[$params_NPOL],     {TYPE => SQL_INTEGER } );
##	$sth->bind_param( 24, $results[$params_NBIN],     {TYPE => SQL_INTEGER } );    
##	$sth->bind_param( 25, $results[$params_NSUB],     {TYPE => SQL_INTEGER } );
##	$sth->bind_param( 26, $results[$params_TSAMP],    {TYPE => SQL_FLOAT } );
##	$sth->bind_param( 27, $results[$params_NBITS],    {TYPE => SQL_TINYINT } );
##	$sth->bind_param( 28, $nbeam,                     {TYPE => SQL_TINYINT } );
##	$sth->bind_param( 29, $results[$params_CNFG],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 30, $results[$params_INST],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 31, $results[$params_RCVR],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 32, $results[$params_HDRVER],   {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 33, $survey,                    {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 34, $results[$params_TELESCOP], {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 35, $results[$params_SITE],     {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 36, $results[$params_OBSRVR],   {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 37, $path,                      {TYPE => SQL_VARCHAR } );
##	$sth->bind_param( 38, $file_size,                 {TYPE => SQL_INTEGER } );
#
#	$sth->execute();
##DEBUGGING
##	DBI::dump_results($sth);
##	$rows = $sth->dump_results();
##	print "$rows\n";
#	print "$sth\n";
#
#    # $db_handle->commit(); # Autocommit is already ON so no need for a manual commit
#    };

    if( $@ ) {
	print "warning statement\n";
	warn "Database error: $DBI::errstr\n";
	$db_handle->rollback(); #just die if rollback is failing
    }
	
	
#DEBUGGING
    $sth->finish();

}

#######################################################

sub isDuplicate {

#DEBUGGING 
#    return 0;

    my $filename = $_[0];
    my $search_data_loc;
    my $search_filename;
    my $pulsar_name;
    
    if ( $filename =~ m/(.*([J,B]{0,1}[0-9]{4}[+-][0-9]{1,4}.*))[\/]+([a-z][0-9]{6}_[0-9]{6}\.([a-z]{2,}))$/i ) {
	$search_data_loc = $1;
	$search_filename = $3;
	$pulsar_name = $2;
    }
    elsif ($filename =~ m/(.*([J,B]{0,1}[0-9]{4}[+-][0-9]{1,4}.*))[\/]+([0-9]{4}-[0-9]{2}-[0-9]{2}-[0-9]{2}:[0-9]{2}:[0-9]{2})$/i ) {
	$search_data_loc = $1;
	$search_filename = $3;
	$pulsar_name = $2;
    }
    else {
	print "$filename is an oddball -- not doing...\n";
	return 1;
    }
    
    my $sql;
    if ($pulsar_name =~ m/.*_R$/) {
	
	$sql = qq{ SELECT * from cals 
		       WHERE data_loc LIKE ? AND 
		       filename    = ?
		   };
    }
    else {
	print "This is an observation\n";
	$sql = qq{ SELECT * from observations 
		       WHERE data_loc LIKE ? AND 
		       filename    = ?
		   };
    }
    
    my $sth = $db_handle->prepare( $sql );
    
    eval {
	print "like $search_data_loc and filename = $search_filename \n";
	
	$sth->bind_param( 1, $search_data_loc, SQL_VARCHAR );
	$sth->bind_param( 2, $search_filename, SQL_VARCHAR );
	$sth->execute();
	
    };
    
    if( $@ ) {
	warn "Database error: $DBI::errstr\n";
	$db_handle->rollback(); #just die if rollback is failing
    }
    
    # read the records
    my @data = $sth->fetchrow_array();
    
    $sth->finish();
    
    if (scalar(@data) > 0) {
	return 1;
    }
    
    return 0;

}
#######################################################

# Removes 
sub deleteStaleRecords {

    my $sql, my $sth, my @data, my $file;
    
    $sql = qq{ SELECT data_loc, filename from cals };
    $sth = $db_handle->prepare( $sql );
    
    eval {
	
	$sth->execute();
	
    };
    
    if( $@ ) {
	warn "Database error: $DBI::errstr\n";
	$db_handle->rollback(); #just die if rollback is failing
    }
    
    my @cals2delete;
    
    while (@data = $sth->fetchrow_array()) {
	
	if (scalar(@data) == 2) {
	    $file = $data[0]."/".$data[1];
	    
	    if (!-e $file) {
		print "$file does NOT exist\n\n";
		
		# Push the row into the cals to delete
		# So array will look like:
		# {/full/path/to/dir, filename.cf, /full/path/to/dir, filename2.cf, etc..}
		push (@cals2delete, @data);
	    }
	}
	
    }
    
    $sql = qq{ SELECT data_loc, filename from observations };
    
    $sth = $db_handle->prepare( $sql );
    eval {
	
	$sth->execute();
	
    };
    
    if( $@ ) {
	warn "Database error: $DBI::errstr\n";
	$db_handle->rollback(); #just die if rollback is failing
    }
    
    my @obs2delete;
    
    while (@data = $sth->fetchrow_array()) {
  	
	if (scalar(@data) == 2) {
	    $file = $data[0]."/".$data[1];
	    
	    if (!-e $file) {
		print "$file does NOT exist\n\n";
		
		# Push the row into the cals to delete
		# So array will look like:
		# {/full/path/to/dir, filename.cf, /full/path/to/dir, filename2.cf, etc..}
		push (@obs2delete, @data);
	    }
	}	          
    }
    
    print "There are " . scalar(@cals2delete)/2 . " stale cals and " . 
	scalar(@obs2delete)/2 . " stale obs to delete\n";
    
    # Delete the rows
    my $data_loc, my $filename;
    
    while (@cals2delete) {
	$data_loc = shift @cals2delete;
	$filename = shift @cals2delete;
	
	$sql = qq{ DELETE FROM cals WHERE data_loc = ? AND filename = ?};
	
	$sth = $db_handle->prepare( $sql );
  	eval {
	    
	    $sth->bind_param( 1, $data_loc, SQL_VARCHAR );
	    $sth->bind_param( 2, $filename, SQL_VARCHAR );
	    $sth->execute();
	    
	};
	
	if( $@ ) {
	    warn "Database error: $DBI::errstr\n";
	    $db_handle->rollback(); #just die if rollback is failing
  	}
	else {
	    print "Successfully deleted $data_loc/$filename\n";
	}
	
    }
    
    while (@obs2delete) {
	$data_loc = shift @obs2delete;
	$filename = shift @obs2delete;
	
	$sql = qq{ DELETE FROM observations WHERE data_loc = ? AND filename = ?};
	
	$sth = $db_handle->prepare( $sql );
	
  	eval {
	    
	    $sth->bind_param( 1, $data_loc, SQL_VARCHAR );
	    $sth->bind_param( 2, $filename, SQL_VARCHAR );
	    $sth->execute();
	    
	};
	
	if( $@ ) {
	    warn "Database error: $DBI::errstr\n";
	    $db_handle->rollback(); #just die if rollback is failing
  	}
	else {
	    print "Successfully deleted $data_loc/$filename\n";
	}
    }
    
    $sth->finish();
    
}



#######################################################

sub connectdb {
	
    # Connect to the MySQL server
    my $dbh = DBI->connect("dbi:mysql:database=psrchive;host=localhost", "psrdba", "lighthouse")
    	or die "Couldn't connect to database: $DBI::errstr\n";
    
    print "Successfully connected to db\n" if $dbh;
    return $dbh;

# DEBUGGING	
#    print "We would normally connect to the DB here\n";
#    return 1;
}