#!/usr/bin/perl
use strict;
use XML::LibXML;
use DBI;
use LWP::UserAgent;

####config specific

#load database and path info
my ($env) = @ARGV;

# See note below about what to expect from this DB.
my $xp_env = XML::LibXML->new->parse_file("../environment_xenia_$env.xml");

my $db_host  = $xp_env->findvalue('//db/host');
my $db_name   = $xp_env->findvalue('//db/name');
my $db_user   = $xp_env->findvalue('//db/user');
my $db_passwd = $xp_env->findvalue('//db/passwd');

my $path_psql = $xp_env->findvalue('//path/psql');


my @platform = qw(1 http://cdmo.baruch.sc.edu/coopcsv/latest/cdmo_northinlet_met.csv met
		2 http://cdmo.baruch.sc.edu/coopcsv/latest/cdmo_northinlet_water.csv water);

my $file_in = './NI.csv';
my $line_read = 2;
my $sql_out = './NI.sql';

#note $m_date is particular to the file setup
#note the @platform and @obs which are specific to the file_type and setup

####

open (SQL_OUT,">$sql_out");

#daylight savings time consideration
my ($temp_sec,$temp_min,$temp_hour,$temp_mday,$temp_mon,$temp_year,$temp_wday,$temp_yday,$isdst) = localtime(time);
my $time_add;
if ($isdst) {$time_add = 4; } else {$time_add = 5; }

#establish database connection
my ($dbh,$sth,$sql);
if ($db_host eq '') { $dbh = DBI->connect ("dbi:Pg:dbname=$db_name", "$db_user", "$db_passwd"); } #remove host reference if local
else { $dbh = DBI->connect ("dbi:Pg:dbname=$db_name;host=$db_host", "$db_user", "$db_passwd"); }
if ( !defined $dbh ) {die "Cannot connect to database!\n";}

while (@platform) {

my $platform_id = shift(@platform);
my $url = shift(@platform);
#print "$url\n";
my $file_type = shift(@platform);

my $ua = new LWP::UserAgent;
$ua->timeout(300); #number of seconds before timeout, default is 3 minutes(180 seconds)
my $response = $ua->mirror($url,$file_in) ; if ($response->is_error) { print "couldn't retrieve file\n"; exit 0; }
#print "ok\n";

#swap out newlines for ^M characters
`perl -pi -e "s/\x0D/\n/g" $file_in`;

open (PLATFORM_IN,"$file_in");

$sql = qq{ select platform_handle,fixed_longitude,fixed_latitude from platform where row_id = $platform_id };
#print "sql:".$sql."\n";
$sth = $dbh->prepare( $sql );
$sth->execute();
if ($sth->rows == 0) { next; }
my ($platform_handle,$m_lon,$m_lat) = $sth->fetchrow_array;

my $line_num = 0;
foreach my $line (<PLATFORM_IN>) {
	$line_num++;

        #ignore lines unless the line we need
        if ($line_num != $line_read) { next; }
	#print "line_num:$line_num\n";

	#possible vars used
	my @obs = ();
	my $m_date;
	my ($air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc);
	my ($water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc);

	##file types
	if ($file_type eq 'met') {
        	($m_date,$air_temp,$air_temp_qc,$rh,$rh_qc,$air_pressure,$air_pressure_qc,$wind_speed,$wind_speed_qc,$wind_from_direction,$wind_from_direction_qc,$precipitation,$precipitation_qc,$solar,$solar_qc) = split(/,/,$line);
		@obs = (1,$air_temp,$air_temp_qc,3,$rh,$rh_qc,4,$air_pressure,$air_pressure_qc,5,$wind_speed,$wind_speed_qc,6,$wind_from_direction,$wind_from_direction_qc,7,$precipitation,$precipitation_qc,8,$solar,$solar_qc);
	}
	if ($file_type eq 'water') {
        	($m_date,$water_temp,$water_temp_qc,$water_conductivity,$water_conductivity_qc,$salinity,$salinity_qc,$do_percent,$do_percent_qc,$do_mgl,$do_mgl_qc,$water_level,$water_level_qc,$ph,$ph_qc,$turbidity,$turbidity_qc) = split(/,/,$line);
		@obs = (2,$water_temp,$water_temp_qc,9,$water_conductivity,$water_conductivity_qc,10,$salinity,$salinity_qc,11,$do_percent,$do_percent_qc,12,$do_mgl,$do_mgl_qc,13,$water_level,$water_level_qc,14,$ph,$ph_qc,15,$turbidity,$turbidity_qc);			
	}
	##
	
        $m_date = '20'.substr($m_date,8,2).'-'.substr($m_date,0,2).'-'.substr($m_date,3,2).' '.substr($m_date,11,5).':00';
	print "$m_date\n";

	while (@obs) {

		my $sensor_id = shift(@obs);
		my $m_value = shift(@obs);
		my $qc_level = shift(@obs);

		$sql = qq{ select m_type_id,fixed_z from sensor where row_id = $sensor_id };
		#print "sql:".$sql."\n";
		$sth = $dbh->prepare( $sql );
		$sth->execute();
		if ($sth->rows == 0) { next; }
		my ($m_type_id,$m_z) = $sth->fetchrow_array;

        	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,qc_level) VALUES (nextval('multi_obs_row_id_seq'),now(),now(),'$platform_handle',$sensor_id,$m_type_id,timestamp '$m_date' + interval '$time_add hours',$m_lon,$m_lat,$m_z,$m_value,$qc_level);\n";

	} #while @obs

} #foreach $line

close (PLATFORM_IN);
`rm -f $file_in`;

} # while @platform

$sth->finish;
$dbh->disconnect();
close (SQL_OUT);

`$path_psql -U $db_user -d $db_name -h $db_host -f $sql_out`;

exit 0;