#! /usr/bin/perl -wT

## NAME: get_times.pl
##
## Gets latest RS overpass timestamp from SC web page
##
## This script gets the latest RS timestamps from a SC web page to insert into a local DB.  It then queries the DB for the overapss tiemstamps for various hourly offsets.
## These offset text files are used for labeling on the NCCOOS Interactive Map and  also for labeling the RS images generated by SEACOOS cachebot.

use lib ".";
use LWP::Simple;
use Date::Format;
use Date::Parse;
use DBI;

$dest_dir='/var/www/html/rs_timestamps/';  

### get html file
$url_base='http://nautilus.baruch.sc.edu/seacoos_misc/show_sea_coos_obs_time_ranges.php';
$target_raw = $dest_dir.'rs_timestamp.html';
getstore($url_base,$target_raw);

### parse through file and cut out relevant timestamps
@layers = ('avhrr_sst','modis_sst','modis_rgb_composite','oi_sst');

if(-e $target_raw)
{

my ($this_layer_time);
foreach (@layers) 
	{
	$this_layer = $_;
	$search_string = '\?layer_name='.$this_layer.'.+';

	open (TIME, $target_raw);
	@t = readline TIME;
	@timestring = grep(/$search_string/, @t);
	close TIME;

	### cut out timestamp string only

	$timestring[0] =~ m/max_time_stamp=(.*?")/;
	$this_layer_time = $1;
	$this_layer_time =~ s/:00"$//;

	if ($this_layer =~ /avhrr_sst/) 
		{
			## insert TS into DB 
			my ($dbh, $sql, $sth);
			$dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
				{
					PrintError => 1,
					RaiseError => 0
				}) || die "$DBI::errstr";

				$sql = "INSERT INTO avhrr_timestamps VALUES ('$this_layer_time')";                                                                                                                          
				$dbh->do( $sql );
				$dbh->disconnect();
		}
	elsif ($this_layer =~ /modis_sst/) 
		{
			## insert TS into DB 
			my ($dbh, $sql, $sth);
			$dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
				{
					PrintError => 1,
					RaiseError => 0
				}) || die "$DBI::errstr";
				
			$sql = "INSERT INTO modis_sst_timestamps VALUES ('$this_layer_time')";                                                                                                                          
			$dbh->do( $sql );
			$dbh->disconnect();
		}
		elsif ($this_layer =~ /modis_rgb_composite/) 
		{
			## insert TS into DB 
			my ($dbh, $sql, $sth);
			$dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
				{
					PrintError => 1,
					RaiseError => 0
				}) || die "$DBI::errstr";
			$sql = "INSERT INTO modis_rgb_timestamps VALUES ('$this_layer_time')";                                                                                                                          
			$dbh->do( $sql );
			$dbh->disconnect();
		}
		elsif ($this_layer =~ /oi_sst/) {
			## insert TS into DB 
			my ($dbh, $sql, $sth);
			$dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
				{
					PrintError => 1,
					RaiseError => 0
				}) || die "$DBI::errstr";
			$sql = "INSERT INTO oi_sst_timestamps VALUES ('$this_layer_time')";                                                                                                                          
			$dbh->do( $sql );
			$dbh->disconnect();
		}
		else 
		{}
	}

## Timestamps extraction for cached image labels
my ($timestamp,$raw_timestamp,$time,$pass_timestamp);
@offsets = ('2','3','4','5','6','8','7');

foreach (@offsets) 
	{
	$offset = $_;      	

	## Date subtraction and formatting for query
	my ($sec,$min,$hour,$mday,$mon,$year) = gmtime(time-60*60*$offset);
	my $format_year = $year + 1900;
	my $format_month = sprintf ("%02d",$mon+1);
	my $format_day = sprintf ("%02d",$mday);
	my $format_hour = sprintf ("%02d",$hour);

	# format query timestamp
	$timestamp = $format_year."_".$format_month."_".$format_day."_".$format_hour."_".$min."_00";
	
	@layers = ('avhrr','modis_sst','modis_rgb','quikscat');
	foreach (@layers) 
		{

		$this_layer = $_;
	
		## lookup time in DB
		my ($dbh, $sql, $sth);
		$dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'lookup', 'lookup',
			{
				PrintError => 1,
				RaiseError => 0
			}) || die "$DBI::errstr";

		#select data from DB
		my $this_table = $this_layer."_timestamps";
		$sql= "select $this_layer from $this_table"
		." where abs(extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS'))"
		." - extract(epoch from $this_layer))"
		." = (select min(abs((extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS')))"
		." - extract(epoch from $this_layer)))"
		." from $this_table)"
		." and abs(extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS'))"
		." - extract(epoch from $this_layer))"
		." <= 60*60*24*2";

		$sth= $dbh->prepare( $sql );
		$sth->execute();
		$sth->bind_columns( \$raw_timestamp );
		$sth->fetch();

		$sth->finish;
		$dbh->disconnect;
		
		$time = str2time($raw_timestamp);
		$pass_timestamp = time2str("%m/%d/%Y %R", $time);
			
		if ($this_layer =~ /avhrr/)                                                                                                                                               
                	{
				open (FOO, ">$dest_dir/avhrrsst$offset.txt");
				print {FOO } $pass_timestamp." UTC";
				close FOO;
			}
		elsif ($this_layer =~ /modis_sst/)                                                                                                                                            
			{
				open (FOO, ">$dest_dir/modissst$offset.txt");                                                                                                           
				print {FOO } $pass_timestamp." UTC";                                                                                                                                              
				close FOO;     
			}		
		elsif ($this_layer =~ /modis_rgb/)                                                                                                                                            
                	{	
				open (RGB, ">$dest_dir/modisrgb$offset.txt");
				print {RGB } $pass_timestamp." UTC+";
				close RGB;

				open (CA, ">$dest_dir/modisca$offset.txt");                                                                                                                    
				print {CA } $pass_timestamp." UTC";                                                                                                 
				close CA;

				open (ERGB, ">$dest_dir/modisergb$offset.txt");                                                                                                                    
				print {ERGB } $pass_timestamp." UTC";                                                                                                                   
				close ERGB;                                                                                                                        
			}
			elsif ($this_layer =~ /quikscat/)                                                                                                                                            
			{
				open (FOO, ">$dest_dir/quikscat$offset.txt");                                                                                                           
				print {FOO } $pass_timestamp." UTC";                                                                                                                                              
				close FOO;
			}
		}	
	}
}

exit 0;