#!/usr/bin/perl
use strict;

use DBI;

my ($dbh, $sql, $sth);
my $db_name = 'xxx';
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,">./nws.sql");

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

foreach my $current_file (@ARGV) {

#print $current_file."\n";

open (PLATFORM_IN,"$current_file");

foreach my $line (<PLATFORM_IN>) {

        #ignore lines which do not start with '0'
        #if (!($line =~ /^0/)) { next; }

	#parse data
        my @record = split(/\s+/,$line);

        $m_date = @record[1].'-'.@record[2].'-'.@record[3].' '.@record[4].':'.@record[5].':00';

	my $platform_name = @record[0];
	my $wind_from_direction = @record[6];
        my $wind_speed = @record[7];
        my $wind_gust = @record[8];
        my $air_temperature = @record[9];
        my $air_pressure = @record[11];

        #print "m_date:$m_date\n";
        #print "wind_speed:$wind_speed\n";


	#query platform
	$sql = qq{ SELECT row_id,platform_handle,fixed_longitude,fixed_latitude FROM platform where platform_handle like 'nws_$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 ($wind_speed ne '') { &insert_data(13,$wind_speed); }
        if ($wind_gust ne '') { &insert_data(14,$wind_gust); }
        if ($wind_from_direction ne '') { &insert_data(15,$wind_from_direction); }
        if ($air_temperature ne '') { &insert_data(16,$air_temperature); }
        if ($air_pressure ne '') { &insert_data(17,$air_pressure); }

} #foreach $line

close (PLATFORM_IN);

} #foreach $current_file

close (SQL_OUT);

$dbh->disconnect();

`/usr/local/pgsql/bin/psql -U $db_user -d $db_name -h $db_host -f nws.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";

}