#!/usr/bin/perl
use strict;

=comment

This program assumes creates a netcdf file to be pulled by the seacoos data loader/aggregation scripts

=cut

use DBI;
use Config::IniFiles;
use Math::Trig;

my ($cfg, $db_name, $db_user, $db_passwd);
my ($sql, $sth);
my ($dbh_wls, $dbh_buoy);

my $qc_flag;

$cfg = new Config::IniFiles( -file => "./processBuoys.ini" );

$db_name = $cfg->val('DB_WLS', 'db_name');
$db_user = $cfg->val('DB_WLS', 'db_user');
$db_passwd = $cfg->val('DB_WLS', 'db_passwd');

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

$db_name = $cfg->val('DB', 'db_name');
$db_user = $cfg->val('DB', 'db_user');
$db_passwd = $cfg->val('DB', 'db_passwd');

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

my $platform_id = $ARGV[0];
my $measurement_date = $ARGV[1];

my $platform_ref;
my ($netcdf_header, $netcdf_final);
my $file_ref;
my $content;
my $record_created = 0;

print $platform_id."\n";
print $measurement_date."\n";

my $measurement_date_query;
if ($measurement_date eq 'latest') {
        $measurement_date_query = 'order by <TIME_ID> desc limit 1';
}
else {
        $measurement_date_query = "and <TIME_ID> = '$measurement_date'";
}

if (($platform_id eq 'buoy2') || ($platform_id eq 'buoy4') || ($platform_id eq 'buoy6') 
     || ($platform_id eq 'buoy5') || ($platform_id eq 'buoy7')) {

	$record_created = 1;

	if ($platform_id eq 'buoy2') { $platform_ref = 'cc_buoy2'; $file_ref = 'FRP2_buoy'; $netcdf_header = './netcdf/FRP2_header.txt'; $netcdf_final = './netcdf/FRP2_final.txt'; }
	if ($platform_id eq 'buoy4') { $platform_ref = 'cc_buoy4'; $file_ref = 'CAP2_buoy'; $netcdf_header = './netcdf/CAP2_header.txt'; $netcdf_final = './netcdf/CAP2_final.txt'; }
	if ($platform_id eq 'buoy6') { $platform_ref = 'cc_buoy6'; $file_ref = 'SUN2_buoy'; $netcdf_header = './netcdf/SUN2_header.txt'; $netcdf_final = './netcdf/SUN2_final.txt'; }

	if ($platform_id eq 'buoy5') { $platform_ref = 'cc_buoy5'; $file_ref = 'CAP3_buoy'; $netcdf_header = './netcdf/CAP3_header.txt'; $netcdf_final = './netcdf/CAP3_final.txt'; }
	if ($platform_id eq 'buoy7') { $platform_ref = 'cc_buoy7'; $file_ref = 'SUN3_buoy'; $netcdf_header = './netcdf/SUN3_header.txt'; $netcdf_final = './netcdf/SUN3_final.txt'; }

	open (NETCDF_FILE, ">$netcdf_final");
	$content = `cat $netcdf_header`;

	#depth
	$sql = qq{ SELECT pcat_pressure,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
	#print $sql."\n";
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my $depth;
	($depth,$measurement_date) = $sth->fetchrow_array;
        $sth->finish;

	my $time_sec = `date --date='$measurement_date +0000' +%s`; 
	chomp($time_sec);

	$content =~ s/<DATETIME>/$measurement_date/g;
	$content =~ s/<TIME>/$time_sec/g;
	
	#if (!$depth) { $depth = 10; }

	my $depth_surface;
	if (($platform_id eq 'buoy5') || ($platform_id eq 'buoy7')) {
		$depth_surface = $depth - 22 ;
	}
	else { #buoy2, buoy4, buoy6
		$depth_surface = $depth - 7.5 ;  
	}
	

	$content =~ s/<DEPTH>/$depth/g;
	$content =~ s/<DEPTH_SURFACE>/$depth_surface/g;

	#wind speed, direction, gust
	$sql = qq{ SELECT ws,wd,wg,<TIME_ID> FROM wind where station = '$platform_ref' $measurement_date_query };
	$sql =~ s/<TIME_ID>/datetime/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($wind_speed, $wind_direction, $wind_gust) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<WIND_SPEED>/$wind_speed/g;
	$content =~ s/<WIND_FROM_DIRECTION>/$wind_direction/g;
	$content =~ s/<WIND_GUST>/$wind_gust/g;

	#air_temperature	
	$sql = qq{ SELECT at,<TIME_ID> FROM air_temperature where station = '$platform_ref' and qc_flag like 'O:R:%' $measurement_date_query };
	$sql =~ s/<TIME_ID>/datetime/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($air_temp) = $sth->fetchrow_array;
        $sth->finish;

	if (!$air_temp) { $air_temp = -99999; }
	$content =~ s/<AIR_TEMPERATURE>/$air_temp/g;

	#air_pressure	
	$sql = qq{ SELECT bp,<TIME_ID> FROM barometric_pressure where station = '$platform_ref' $measurement_date_query };
	$sql =~ s/<TIME_ID>/datetime/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($air_pressure) = $sth->fetchrow_array/1000; 
        $sth->finish;
	$content =~ s/<AIR_PRESSURE>/$air_pressure/g;

	#relative humidity	
	$sql = qq{ SELECT measurement_value,<TIME_ID> FROM humidity where station_id = '$platform_ref' $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($relative_humidity) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<RELATIVE_HUMIDITY>/$relative_humidity/g;

	#solar radiation	
	$sql = qq{ SELECT solar_obs,<TIME_ID> FROM solar where station_id = '$platform_ref' $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($solari_radiation) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SOLAR_RADIATION>/$solari_radiation/g;

	if ($platform_id eq 'buoy4') {
	#visibility	
	$sql = qq{ SELECT measurement_value,<TIME_ID> FROM visibility where station_id = '$platform_ref' $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_wls->prepare( $sql );
        $sth->execute();
	my ($visibility) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<VISIBILITY>/$visibility/g;
	}

	if (($platform_id eq 'buoy5') || ($platform_id eq 'buoy7')) {

	#sea surface chlorophyll	
	$sql = qq{ SELECT fcat_voltage,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_surface_chl_concentration) = $sth->fetchrow_array;
	
	if ($platform_id eq 'buoy5') { $sea_surface_chl_concentration = sprintf("%.1f", $sea_surface_chl_concentration*14.40); }
	if ($platform_id eq 'buoy7') { $sea_surface_chl_concentration = sprintf("%.1f", $sea_surface_chl_concentration*12.84); }
	
        $sth->finish;
	$content =~ s/<SEA_SURFACE_CHL_CONCENTRATION>/$sea_surface_chl_concentration/g;
	$content =~ s/<CHL_CONCENTRATION>/$sea_surface_chl_concentration/g;	
	
	#sea surface salinity	
	$sql = qq{ SELECT fcat_salinity,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_surface_salinity) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_SURFACE_SALINITY>/$sea_surface_salinity/g;
	$content =~ s/<SALINITY>/$sea_surface_salinity/g;

	#sea surface temperature	
	$sql = qq{ SELECT fcat_temp,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_surface_temperature) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_SURFACE_TEMPERATURE>/$sea_surface_temperature/g;
	$content =~ s/<WATER_TEMPERATURE>/$sea_surface_temperature/g;
	}

	#sea bottom salinity	
	$sql = qq{ SELECT pcat_salinity,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_bottom_salinity) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_BOTTOM_SALINITY>/$sea_bottom_salinity/g;
	$content =~ s/<SALINITY>/$sea_bottom_salinity/g;

	#sea bottom temperature	
	$sql = qq{ SELECT pcat_temp,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_bottom_temperature) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_BOTTOM_TEMPERATURE>/$sea_bottom_temperature/g;
	$content =~ s/<WATER_TEMPERATURE>/$sea_bottom_temperature/g;

	#sea bottom current speed	
	$sql = qq{ SELECT adcp_bin1_velocity,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_bottom_current_speed) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_BOTTOM_CURRENT_SPEED>/$sea_bottom_current_speed/g;

	#sea bottom current to direction	
	$sql = qq{ SELECT adcp_bin1_direction,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
	my ($sea_bottom_current_to_direction) = $sth->fetchrow_array;
        $sth->finish;
	$content =~ s/<SEA_BOTTOM_CURRENT_TO_DIRECTION>/$sea_bottom_current_to_direction/g;

	my ($sea_bottom_eastward_current) = sprintf("%.1f", $sea_bottom_current_speed * sin(deg2rad($sea_bottom_current_to_direction)));
	$content =~ s/<SEA_BOTTOM_EASTWARD_CURRENT>/$sea_bottom_eastward_current/g;

	my ($sea_bottom_northward_current) = sprintf("%.1f", $sea_bottom_current_speed * cos(deg2rad($sea_bottom_current_to_direction)));
	$content =~ s/<SEA_BOTTOM_NORTHWARD_CURRENT>/$sea_bottom_northward_current/g;

	my ($adcp_bin_velocity, $adcp_bin_direction);
	if (($platform_id eq 'buoy5') || ($platform_id eq 'buoy7')) {
		$adcp_bin_velocity = 'adcp_bin8_velocity';
		$adcp_bin_direction = 'adcp_bin8_direction';
	}
	else { #buoy2, buoy4, buoy6
		$adcp_bin_velocity = 'adcp_bin6_velocity';
		$adcp_bin_direction = 'adcp_bin6_direction';	
	}


        #sea surface current speed
	$sql = qq{ SELECT $adcp_bin_velocity,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
        my ($sea_surface_current_speed) = $sth->fetchrow_array;
        $sth->finish;
        $content =~ s/<SEA_SURFACE_CURRENT_SPEED>/$sea_surface_current_speed/g;
        $content =~ s/<CURRENT_SPEED>/$sea_surface_current_speed/g;

        #sea surface current to direction
	$sql = qq{ SELECT $adcp_bin_direction,<TIME_ID> FROM $platform_id where 1=1 $measurement_date_query };
	$sql =~ s/<TIME_ID>/measurement_date/g;
        $sth = $dbh_buoy->prepare( $sql );
        $sth->execute();
        my ($sea_surface_current_to_direction) = $sth->fetchrow_array;
        $sth->finish;
        $content =~ s/<SEA_SURFACE_CURRENT_TO_DIRECTION>/$sea_surface_current_to_direction/g;
        $content =~ s/<CURRENT_TO_DIRECTION>/$sea_surface_current_to_direction/g;

	my ($sea_surface_eastward_current) = sprintf("%.1f", $sea_surface_current_speed * sin(deg2rad($sea_surface_current_to_direction)));
	$content =~ s/<SEA_SURFACE_EASTWARD_CURRENT>/$sea_surface_eastward_current/g;
	$content =~ s/<EASTWARD_CURRENT>/$sea_surface_eastward_current/g;

	my ($sea_surface_northward_current) = sprintf("%.1f", $sea_surface_current_speed * cos(deg2rad($sea_surface_current_to_direction)));
	$content =~ s/<SEA_SURFACE_NORTHWARD_CURRENT>/$sea_surface_northward_current/g;
	$content =~ s/<NORTHWARD_CURRENT>/$sea_surface_northward_current/g;


	print NETCDF_FILE $content;

	close (NETCDF_FILE);
}

$dbh_wls->disconnect();
$dbh_buoy->disconnect();

if ($record_created) {
my $timestamp_prefix = `date --date='$measurement_date'  +"%Y%m%d%H%M%S"`;
chomp($timestamp_prefix);

my $filename = 'carocoops_'.$file_ref."_".$timestamp_prefix."_latest.nc";

`/usr2/home/jcothran/storm_surge/ncgen -o /usr2/prod/buoys/processed/netcdf/$filename $netcdf_final`;
}

exit 0;