#!/usr/bin/perl
use strict;

use DBI;

my ($dbh, $sql, $sth);
my $db_name = 'db_multi_obs_carocoops';
my $db_host = 'xxx.xxx.xxx.xxx';
my $db_user = 'xxx';

#establish database connection
$dbh = DBI->connect ( "dbi:Pg:dbname=$db_name;host=$db_host", "$db_user", "");
if ( !defined $dbh ) {
  die "Cannot connect to database!\n";
}

open (SQL_OUT,">./carocoops.sql");

my ($platform_id,$platform_handle,$m_date,$m_lon,$m_lat);

foreach my $current_file (@ARGV) {

#print $current_file."\n";
my $currentPath = './';


open (PLATFORM_IN,"$current_file");


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

if (($current_file =~ /Buoy10-/)) {

	my ($buoy_id, $buoy_lat, $buoy_long, $buoy_battery, $buoy_temp);
	my $measurement_timestamp_dbformat; #for ndbc fm13 message

	$buoy_id = 'buoy4';

	print "$ARGV[0] processed\n";

	#replace empty ADCP statement with appropriate number of empty fields
	`sed -e 's/ADCP,,/ADCP,0,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,/' $currentPath$current_file > processBuoyTemp1 && mv processBuoyTemp1 $currentPath$current_file`;

	#replace 'termination' string with empty field from there forward

	open(BUOY_FILE, $currentPath.$current_file)
	or die "Can't find file $currentPath$current_file: $!\n";

	#the following code reads 0,1,many lines of buoy updates, ignoring lines starting with # and partial lines
	foreach my $line_record (<PLATFORM_IN>) {

		#ignore lines which start with '#'
		if (substr($line_record,0,1) eq '#') {next};

		#@record = split(/\,/,$line_record);
		my @init_record = split(/\,/,$line_record);

		my @record = &removeTerminationData(@init_record);
		#print "#records = ".scalar(@record)."\n";

		#ignore lines with date only - technically should be able to mark records that are being rebroadcast, but issues exist
		if (scalar(@record) < 3) {next};

		#skip lines where the first field(buoy id) or more might have gotten dropped
		if (@record[8] ne 'PCAT') { print "row beginning truncated \n"; next; } #using the 'PCAT' field here as a check

                if ($buoy_id eq 'buoy2') { if (!(@record[5] =~ /3216./)) { next; }} #double checking latitude position
	
		if ($buoy_id eq 'buoy20' || $buoy_id eq 'buoy21') { splice(@record, 13, 1); } #not sure what this extra field represents
	
		#good to go here
		my $valid_fields = 2;

		#assign record array fields to variables
		my $measurement_date = @record[1];	
		my $measurement_time = @record[2]; #note this is data logger time - assuming this is 'close enough' to UTC

		#$measurement_timestamp_webformat = substr($measurement_date,3,5)." ".substr($measurement_time,0,5);
		#print $measurement_timestamp_webformat."\n";

		$measurement_timestamp_dbformat = substr($measurement_date,3,5)."/".substr($measurement_date,0,2)." ".$measurement_time;
		#print $measurement_timestamp_dbformat."\n";
		if ($measurement_timestamp_dbformat eq '') {$measurement_timestamp_dbformat = 'NULL'};

		my $measurement_timestamp_webformat = get_local_time($measurement_timestamp_dbformat, -4); 
		print $measurement_timestamp_webformat."\n";

		$buoy_battery = 'NULL';
		$buoy_temp = 'NULL';
		$buoy_lat = 'NULL';
		$buoy_long = 'NULL';
		my $utc = 'NULL';
	
                if (scalar( (@record) > 2) && (!(@record[3] eq '' || @record[3] eq 'NODATA')) ) {
                        $buoy_battery = @record[3];
                        $valid_fields++;
                }

                if (scalar( (@record) > 3) && (!(@record[4] eq '' || @record[4] eq 'NODATA')) ) {
                        $buoy_temp = @record[4];
                        $valid_fields++;
                }

                if (scalar( (@record) > 4) && (!(@record[5] eq '' || @record[5] eq 'NODATA')) ) { 
                        $buoy_lat = @record[5];
                        $valid_fields++;
                }

                if (scalar( (@record) > 5) && (!(@record[6] eq '' || @record[6] eq 'NODATA')) ) {
                        $buoy_long = @record[6];
                        $valid_fields++;
                }

                if (scalar( (@record) > 6) && (!(@record[7] eq '' || @record[7] eq 'NODATA')) ) { 
                        $utc = @record[7];
                        $valid_fields++;
                }


		#PCAT

		my $pcat_pressure = 'NULL';
		my $pcat_conductivity = 'NULL';
		my $pcat_temp = 'NULL';
		my $pcat_salinity = 'NULL';

		print "PCAT\n";

		if (scalar( (@record) > 8) && (!(@record[9] eq '' || @record[9] eq 'NODATA')) ) {		
			$pcat_pressure = @record[9];
			$valid_fields++;
		}
		
		if (scalar( (@record) > 9) && (!(@record[10] eq '' || @record[10] eq 'NODATA')) ) {		
			$pcat_conductivity = sprintf("%.4f", @record[10]);
			$valid_fields++;
		}

		if (scalar( (@record) > 10) && (!(@record[11] eq '' || @record[11] eq 'NODATA')) ) {		
			$pcat_temp = @record[11];
			$valid_fields++;
		}

		if (scalar( (@record) > 11) && (!(@record[12] eq '' || @record[12] eq 'NODATA')) ) {		
			$pcat_salinity = @record[12];
			$valid_fields++;
		}

		##########
		#weatherpack variables
		
		my $wxpak_wind_speed = 'NULL';   #default unit is knots
		my $wxpak_wind_speed_mps = 'NULL';
		my $wxpak_wind_direction = 'NULL';
		my $wxpak_wind_gust = 'NULL'; #default unis is knots
		my $wxpak_wind_gust_mps = 'NULL'; 
                
                my $wxpak_air_temp = 'NULL';

		my $wxpak_humidity ='NULL';

		my $wxpak_air_pressure = 'NULL';

		my $wxpak_solar = 'NULL';

		my $marker = "";
		$marker = test_array_for_string(\@record, 'WXPAK');
		
		if ($marker) {

		print "WXPAK\n";

		#offset config
		my $wind_speed_offset = "";
		my $wind_direction_offset = "";
		my $wind_gust_offset = "";
		my $air_temp_offset = "";
		my $humidity_offset = "";
		my $air_pressure_offset = "";
		my $solar_offset = "";
		my $visibility_offset = "";

		my $wind_speed_units = "";

		if ($buoy_id eq 'buoy2' || $buoy_id eq 'buoy4' || $buoy_id eq 'buoy5') {
		$wind_speed_offset = 9;
		$wind_direction_offset = 10;
		$wind_gust_offset = 11;
		$air_temp_offset = 4;
		$humidity_offset = 5;
		$air_pressure_offset = 6;
		$solar_offset = 7;
		$visibility_offset = 12;

		$wind_speed_units = "knots";
		}
		else {  #all the other buoys
		$wind_speed_offset = 4;
		$wind_direction_offset = 5;
		$wind_gust_offset = 7;
		$air_temp_offset = 8;
		$humidity_offset = 9;
		$air_pressure_offset = 10;
		$solar_offset = 11;
		$visibility_offset = 13;

		$wind_speed_units = "mps";
		}

                if (scalar( (@record) > $marker+$wind_speed_offset-1) && (!(@record[$marker+$wind_speed_offset] eq '' || @record[$marker+$wind_speed_offset] eq 'NODATA')) ) {   
                        $wxpak_wind_speed = @record[$marker+$wind_speed_offset];
			#if ($wind_speed_units eq 'mps') { $wxpak_wind_speed = sprintf("%.1f", $wxpak_wind_speed*1.9438); }
			if ($wind_speed_units eq 'knots') { $wxpak_wind_speed_mps = sprintf("%.1f", $wxpak_wind_speed*0.51); }
			if ($wind_speed_units eq 'mps') { $wxpak_wind_speed_mps = $wxpak_wind_speed; $wxpak_wind_speed = sprintf("%.1f", $wxpak_wind_speed*1.9478); }
                        $valid_fields++;
                }

                if ($wxpak_wind_speed ne 'NULL' and $wxpak_wind_speed ne '0.0') {
                        if (scalar( (@record) > $marker+$wind_direction_offset-1) && (!(@record[$marker+$wind_direction_offset] eq '' || @record[$marker+$wind_direction_offset] eq 'NODATA')) ) {
                                $wxpak_wind_direction = @record[$marker+$wind_direction_offset];
                                $valid_fields++;
                        }

                        if (scalar( (@record) > $marker+$wind_gust_offset-1) && (!(@record[$marker+$wind_gust_offset] eq '' || @record[$marker+$wind_gust_offset] eq 'NODATA')) ) {
                                $wxpak_wind_gust = @record[$marker+$wind_gust_offset];
				if ($wind_speed_units eq 'knots') { $wxpak_wind_gust_mps = sprintf("%.1f", $wxpak_wind_gust*0.51); }
				if ($wind_speed_units eq 'mps') { $wxpak_wind_gust_mps = $wxpak_wind_gust; $wxpak_wind_gust = sprintf("%.1f", $wxpak_wind_speed*1.9478); }
                                $valid_fields++;
                        }

                }

                if (scalar( (@record) > $marker+$air_temp_offset-1) && (!(@record[$marker+$air_temp_offset] eq '' || @record[$marker+$air_temp_offset] eq 'NODATA')) ) {
                        	$wxpak_air_temp = @record[$marker+$air_temp_offset];
				print "air_temp:".$wxpak_air_temp."\n";
				$valid_fields++;
			}

		if (scalar( (@record) > $marker+$humidity_offset-1) && (!(@record[$marker+$humidity_offset] eq '' || @record[$marker+$humidity_offset] eq 'NODATA')) ) {		
			$wxpak_humidity = @record[$marker+$humidity_offset];
			$valid_fields++;
		}	

		if (scalar( (@record) > $marker+$air_pressure_offset-1) && (!(@record[$marker+$air_pressure_offset] eq '' || @record[$marker+$air_pressure_offset] eq 'NODATA')) ) {		
				$wxpak_air_pressure = @record[$marker+$air_pressure_offset];
				$valid_fields++;
		}

		if (scalar( (@record) > $marker+$solar_offset-1) && (!(@record[$marker+$solar_offset] eq '' || @record[$marker+$solar_offset] eq 'NODATA')) ) {		
			$wxpak_solar = @record[$marker+$solar_offset];
			$valid_fields++;
		}
		} #if marker

		##########
		print "valid fields=".$valid_fields."\n";

###########

	my $platform_name = 'CAP2';
        $m_date = $measurement_timestamp_dbformat;
        my $air_temperature = $wxpak_air_temp;

        print "m_date:$m_date\n";


	#query platform
	$sql = qq{ SELECT row_id,platform_handle,fixed_longitude,fixed_latitude FROM platform where platform_handle like 'carocoops_$platform_name%' };
	print "$sql\n";
	$sth = $dbh->prepare( $sql );
	$sth->execute();

	my @stored_record = $sth->fetchrow_array;
	$platform_id = @stored_record[0];
	$platform_handle = @stored_record[1];
	$m_lon = @stored_record[2];
	$m_lat = @stored_record[3];
	$sth->finish;

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

	#insert data based on lookup for observation types on obs_type table

        if ($air_temperature ne '') { &insert_data(16,$air_temperature); }

} #foreach $line_record

close (PLATFORM_IN);

} #if CAP2

} #foreach $current_file

close (SQL_OUT);

$dbh->disconnect();

`/usr/local/pgsql/bin/psql -U $db_user -d $db_name -h $db_host -f carocoops.sql`;

exit 0;

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

sub insert_data {

my ($m_type_id, $m_value) = @_;

#query sensor
$sql = qq{ SELECT row_id,fixed_z FROM sensor where platform_id = $platform_id and m_type_id = $m_type_id and s_order = 1 };
print "$sql\n";
$sth = $dbh->prepare( $sql );
$sth->execute();

my @stored_record = $sth->fetchrow_array;
my $sensor_id = @stored_record[0];
my $m_z = @stored_record[1];
$sth->finish;

print SQL_OUT "INSERT INTO multi_obs (row_id,row_entry_date,row_update_date,platform_handle,sensor_id,m_type_id,m_date,m_lon,m_lat,m_z,m_value) VALUES (nextval('multi_obs_row_id_seq'),now(),now(),'$platform_handle',$sensor_id,$m_type_id,'$m_date',$m_lon,$m_lat,$m_z,$m_value);\n";

}

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

sub get_local_time
{

my ($date, $time_shift) = @_;

#print "date:".$date."\n";
#print "time_shift:".$time_shift."\n";

my ($month, $day, $year, $hour, $minute, $second) = split(/[\/ :]/,$date);

my $time_subtract = -1*$time_shift*3600; #time_shift negative west of gmt
#print "time_subtract:".$time_subtract."\n";

my ($temp_sec,$temp_min,$temp_hour,$temp_mday,$temp_mon,$temp_year,$temp_wday,$temp_yday,$isdst) = localtime(time);

if (!$isdst) {
	$time_subtract = $time_subtract + 3600; #subtract one more hour
}
#print "time_subtract:".$time_subtract."\n";

my $date_converted_1 = `date --date='20$year-$month-$day $hour:$minute:$second +0000' +%s` - $time_subtract;

my $date_converted_2 = `date -u -d '1970-01-01 $date_converted_1 seconds' +"%m/%d %r"`;

my $date_converted_3 = substr($date_converted_2,0,11).substr($date_converted_2,14,3);

#print "date_converted_3:".$date_converted_3."\n";

return $date_converted_3;

}

sub removeTerminationData
{

my @init_record = @_;

#@record = (); 	#clear the array
my @record; 	#a better way of making sure the record is cleared each time

foreach my $field (@init_record) {
	if ($field =~ /termination/) {
		#print "yes\n";
		last;
	}
	else {
		#print $field."\n";
		push(@record, $field); 
	}
}

return @record;
}

sub convertTo360
{
#this just converts negative direction values to their positive counterpart 

my $i = @_[0];

if ( $i < 0 ) {
        $i = 360 + $i ;
}

return $i
}

sub test_array_for_string {

my ($test_array, $test_value) = @_;

my $index;
my $i = 0;
foreach my $element (@{$test_array}) {
        if ($element eq $test_value) {
                $index = $i;
        }
        $i++;
}

return $index;

}