#!/usr/bin/perl

# This script reads from the multi_obs table and creates a file of CSV records for the
# latest readings from each sensor on each platform.

#note 'where m_date > now()' is based on EST/EDT which gives us a 4-5 hour window for latest obs in consideration, will need to add/subtract additional hours for other time zones

use DBI;
use XML::LibXML;
use strict;

#load database and path info
my $env = shift;

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

#####################
##read sql query results into hash

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 $dir_base = $xp_env->findvalue('//path/dir_base');

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

my $sql = qq{
  select m_date
    ,multi_obs.platform_handle
    ,obs_type.standard_name
    ,uom_type.standard_name
    ,multi_obs.m_type_id
    ,m_lon
    ,m_lat
    ,m_z
    ,m_value
    ,qc_level
    ,sensor.url
    ,platform.url
    ,organization.short_name
    ,organization.url
    ,m_type_display_order.row_id
  from multi_obs
    left join sensor on sensor.row_id=multi_obs.sensor_id
    left join m_type on m_type.row_id=multi_obs.m_type_id
    left join obs_type on obs_type.row_id=m_type.obs_type_id
    left join uom_type on uom_type.row_id=m_type.uom_type_id
    left join platform on platform.row_id=sensor.platform_id
    left join organization on organization.row_id=platform.organization_id
    left join m_type_display_order on m_type_display_order.m_type_id=multi_obs.m_type_id
    where m_date > now()
  order by platform_handle,m_type_display_order.row_id,m_date desc;
};

my $sth = $dbh->prepare($sql);
$sth->execute();

while (my (
     $m_date
    ,$platform_handle
    ,$obs_type
    ,$uom_type
    ,$m_type_id
    ,$m_lon
    ,$m_lat
    ,$m_z
    ,$m_value
    ,$qc_level
    ,$sensor_url
    ,$platform_url
    ,$organization_name
    ,$organization_url
    ,$m_type_display_order
  ) = $sth->fetchrow_array) {

  # Since the obs are ordered by time descending, we only need to keep the
  # top times per platform/sensor.  
  # note using $m_type_display_order for a unique obs&uom key 
  my $operator = $organization_name;
  if (!defined $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}) {
    #have to add 'obs_list' for sorting level layer
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_type_id}  = $m_type_id;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{obs_type} = $obs_type;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{uom_type} = $uom_type;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_date}  = $m_date;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_value} = $m_value;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_z}     = $m_z;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{qc_level} = $qc_level;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{url} = $sensor_url;

    #assuming all observations are basically the same lat/lon as platform
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lat} = $m_lat;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lon} = $m_lon;
    $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{url} = $platform_url;

    $latest_obs{operator_list}{$operator}{name} = $organization_name;
    $latest_obs{operator_list}{$operator}{url} = $organization_url;

    print "$platform_handle:$obs_type:$uom_type\n";
  }

}
$sth->finish;
$dbh->disconnect();

#####################
##print XML from hash

open (XML_FILE, ">$dir_base/latest_obs.xml");

print XML_FILE "<latest_obs ";
print XML_FILE "xmlns:georss=\"http://www.georss.org/georss\"";
print XML_FILE ">";

print XML_FILE "<operatorList>";
foreach my $operator (sort keys %{$r_latest_obs->{operator_list}}) {

my $operator_name = $latest_obs{operator_list}{$operator}{name};
my $operator_url = $latest_obs{operator_list}{$operator}{url};

print XML_FILE "<operator>";
print XML_FILE "<operatorName>$operator_name</operatorName>";
print XML_FILE "<operatorURL>$operator_url</operatorURL>";

print XML_FILE "<platformList>";
foreach my $platform_handle (sort keys %{$r_latest_obs->{operator_list}{$operator}{platform_list}}) {

my $m_lat = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lat};
my $m_lon = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lon};
my $platform_url = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{url};

print XML_FILE "<platform>";
print XML_FILE "<platformHandle>$platform_handle</platformHandle>";
print XML_FILE "<platformURL>$platform_url</platformURL>";
print XML_FILE "<georss:point>$m_lat $m_lon</georss:point>";

print XML_FILE "<observationList>";
foreach my $m_type_display_order (sort keys %{$r_latest_obs->{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}}) {
print XML_FILE "<observation>";

my $m_date = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_date};
$m_date =~ s/ /T/g;
$m_date .= 'Z';

my $m_type_id = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_type_id};
my $obs_type = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{obs_type};
my $uom_type = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{uom_type};
my $m_z = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_z};
my $m_value = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_value};
my $qc_level = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{qc_level};
my $sensor_url = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{sensor_url};

print XML_FILE "<m_type_id>$m_type_id</m_type_id>";
print XML_FILE "<type>$obs_type</type>";
print XML_FILE "<uom>$uom_type</uom>";
print XML_FILE "<dateTime>$m_date</dateTime>";
print XML_FILE "<elev>$m_z</elev>";
print XML_FILE "<value>$m_value</value>";
print XML_FILE "<qc_level>$qc_level</qc_level>";
print XML_FILE "<sensorURL>$sensor_url</sensorURL>";

print XML_FILE "</observation>";
}
print XML_FILE "</observationList>";
print XML_FILE "</platform>";
}
print XML_FILE "</platformList>";
print XML_FILE "</operator>";
}
print XML_FILE "</operatorList>";

print XML_FILE "</latest_obs>";
close (XML_FILE);

##zip latest_obs.xml file
`cd $dir_base; zip latest_obs.xml.zip latest_obs.xml`;

#####################
##print platform XML's from hash
##mostly same code as above except individual files per platform (like RSS for each platform)

my ($header,$header_operator,$body,$footer);

$header .= "<latest_obs ";
$header .=  "xmlns:georss=\"http://www.georss.org/georss\"";
$header .=  ">";

foreach my $operator (sort keys %{$r_latest_obs->{operator_list}}) {
$header_operator = '';

my $operator_name = $latest_obs{operator_list}{$operator}{name};
my $operator_url = $latest_obs{operator_list}{$operator}{url};

$header_operator .= "<operator>";
$header_operator .= "<operatorName>$operator_name</operatorName>";
$header_operator .= "<operatorURL>$operator_url</operatorURL>";

foreach my $platform_handle (sort keys %{$r_latest_obs->{operator_list}{$operator}{platform_list}}) {
$body = '';

my $m_lat = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lat};
my $m_lon = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{m_lon};
my $platform_url = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{url};

$body .= "<platform>";
$body .= "<platformHandle>$platform_handle</platformHandle>";
$body .= "<platformURL>$platform_url</platformURL>";
$body .= "<georss:point>$m_lat $m_lon</georss:point>";

$body .= "<observationList>";
foreach my $m_type_display_order (sort keys %{$r_latest_obs->{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}}) {
$body .= "<observation>";

my $m_date = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_date};
$m_date =~ s/ /T/g;
$m_date .= 'Z';

my $m_type_id = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_type_id};
my $obs_type = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{obs_type};
my $uom_type = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{uom_type};
my $m_z = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_z};
my $m_value = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{m_value};
my $qc_level = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{qc_level};
my $sensor_url = $latest_obs{operator_list}{$operator}{platform_list}{$platform_handle}{obs_list}{$m_type_display_order}{sensor_url};

$body .= "<m_type_id>$m_type_id</m_type_id>";
$body .= "<type>$obs_type</type>";
$body .= "<uom>$uom_type</uom>";
$body .= "<dateTime>$m_date</dateTime>";
$body .= "<elev>$m_z</elev>";
$body .= "<value>$m_value</value>";
$body .= "<qc_level>$qc_level</qc_level>";
$body .= "<sensorURL>$sensor_url</sensorURL>";

$body .= "</observation>";
} #observation
$body .= "</observationList>";
$body .= "</platform>";
$body .= "</operator>";
$body .= "</latest_obs>";

open (XML_FILE, ">$dir_base\platform/$platform_handle/xml/latest_obs.xml");
print XML_FILE $header;
print XML_FILE $header_operator;
print XML_FILE $body;
close (XML_FILE);

} #platform
} #operator

exit 0;