#!/usr/bin/perl

# $Revision: 1.7 $

use strict;
use warnings;

use File::Basename;
use LWP::Simple;
use File::Listing qw(parse_dir);
use DateTime;

my $start_time = time;

my $myname = basename $0;

# options
my $download = 1;   # download stations list or use test list
my $update = 1;     # update the database or print SQL to stdout

my $wx_server = "ftp://tgftp.nws.noaa.gov/data/observations/metar/stations/";
my $wx_reports_db = 'wx_reports_db.sqlite';
my $sqlite_prog_path = '/usr/bin/sqlite3';

my $stations_list;

if ($download) {
    $stations_list = get($wx_server) or die "$myname: could not get stations from '$wx_server': @!";
} else {
$stations_list = <<EOT; # for testing SQL generation and DB updating
-rw-r--r--    1 3110     1              57 Nov 16 19:09 AABS.TXT
-rw-r--r--    1 3110     1              56 Jan 13 01:02 AAXX.TXT
-rw-r--r--    1 3110     0              88 Mar 06 11:20 AGGH.TXT
-rw-r--r--    1 3110     1              88 Feb 27 10:31 AGTB.TXT
-rw-r--r--    1 3110     1              62 Dec 25  2007 AKKR.TXT
-rw-r--r--    1 3110     1              62 Dec 18  2007 ALMG.TXT
-rw-r--r--    1 3110     1              69 Dec 09  2007 ANAU.TXT
-rw-r--r--    1 3110     1              73 Dec 01 22:06 ASEZ.TXT
-rw-r--r--    1 3110     1              70 Sep 30 10:34 ASZE.TXT
-rw-r--r--    1 3110     1              96 May 03  2008 ATAR.TXT
-rw-r--r--    1 3110     1              98 Mar 06 06:21 AYGN.TXT
-rw-r--r--    1 3110     1              63 Aug 26  2008 AYMH.TXT
-rw-r--r--    1 3110     0              70 Mar 06 12:00 AYMO.TXT
-rw-r--r--    1 3110     1              82 Mar 06 06:11 AYNZ.TXT
-rw-r--r--    1 3110     0              76 Mar 06 12:00 AYPY.TXT
-rw-r--r--    1 3110     1              69 Mar 06 06:21 AYVN.TXT
-rw-r--r--    1 3110     1              76 Feb 02 01:10 AYWK.TXT
-rw-r--r--    1 3110     1              68 Aug 11  2007 AZTX.TXT
-rw-r--r--    1 3110     1              85 Mar 06 11:11 AZUH.TXT
EOT
}

my $sql = "BEGIN TRANSACTION;\n\n";

$sql .= "CREATE TABLE IF NOT EXISTS reports(";
$sql .= " station_id, datetime_utc,";
$sql .= " PRIMARY KEY (station_id, datetime_utc) );\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_reports AS SELECT *";
$sql .= " FROM reports ORDER BY station_id, datetime_utc;\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_stations AS SELECT station_id, count() AS report_count";
$sql .= " FROM reports GROUP BY station_id ORDER BY station_id;\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_stations_by_report_count AS SELECT *";
$sql .= " FROM v_stations ORDER BY report_count DESC, station_id;\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_report_counts AS SELECT report_count, count() AS frequency";
$sql .= " FROM v_stations GROUP BY report_count ORDER BY report_count DESC;\n\n";

$sql .= "CREATE TABLE IF NOT EXISTS log(";
$sql .= " datetime_utc, num_stations_dl, num_stations_db, elapsed_time_sec";
$sql .= " );\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_log AS SELECT *";
$sql .= " FROM log ORDER BY datetime_utc DESC;\n\n";

$sql .= "CREATE VIEW IF NOT EXISTS v_log_summary AS SELECT count() AS count";
$sql .= " FROM log;\n\n";

my $num_stations_dl;
for (parse_dir($stations_list, 'utc')) {
    #my ($name, $type, $size, $mtime, $mode) = @$_;
    my ($name, undef, undef, $mtime, undef) = @$_;

    (my $name2 = $name) =~ s/\.TXT$//; # strip '.TXT' suffix

    my $dt = DateTime->from_epoch( epoch => $mtime ); # time zone is UTC by default
    my $mtime2 = $dt->strftime("%F %T");

    $sql .= "INSERT OR IGNORE INTO reports VALUES ( '$name2', '$mtime2' );\n";
    $num_stations_dl++;
}

$sql .= "\n";

my $elapsed_time = time - $start_time;

$sql .= "INSERT INTO log SELECT CURRENT_TIMESTAMP, $num_stations_dl, num_stations_db, $elapsed_time";
$sql .= " FROM (SELECT count() AS num_stations_db FROM v_stations);\n\n";

$sql .= "END TRANSACTION;\n\n";

if ($update) {
    open my $sqlite_fh, "| $sqlite_prog_path $wx_reports_db" or die "$myname: Cannot open '$sqlite_prog_path': $!\n";
    print $sqlite_fh $sql;
    close $sqlite_fh or die "$myname: Update failed with sqlite exit code: $?\n";
} else {
    print $sql;
}

exit 0;

