#!/usr/bin/perl

# 20091223 01:27 UTC : There were stupid off-by-one index
# errors all over the place. Hope I have fixed all of them.

use strict; use warnings;
use autodie;

use DBI;
use File::Find;
use File::Slurp;

my $dbh = DBI->connect('dbi:SQLite:metoffice.db', undef, undef, {
    RaiseError => 1, AutoCommit => 0,
});

find(\&wanted, '.');

sub wanted {
    return unless -f and /\A[0-9]+\z/;
    warn "$File::Find::name\n";
    my $data = read_file $_;

    my ($header, $body) = split /Obs:\s+/, $data;
    my ($station_number) = $header =~ /\ANumber=\s+([0-9]+)/;

    my @header    = split /\n/, $header;
    my @meta      = grep { ! /\A(Normals|Standard deviations)=/ } @header;
    my ($normals) = grep { /\ANormals=/ } @header;
    my ($stdevs)  = grep { /\AStandard deviations=/ } @header;

    my %meta;
    for my $item ( @meta ) {
        my ($k, $v) = split /=\s+/, $item;
        $k = join('_', Station => map { ucfirst } split ' ', $k);
        $v =~ s/\A\s+//;
        $v =~ s/\s+\z//;
        $meta{"'$k'"} = $v;
    }
    $dbh->do(
        sprintf(q{INSERT INTO 'meta' (%s) VALUES (%s)},
            join(', ', keys %meta), join(', ', ('?') x keys %meta)
        ),
        {},
        @meta{ keys %meta },
    );

    (undef, $normals) = split /=\s+/, $normals;

    my @normals;
    for my $normal (split /\s+/, $normals) {
        push @normals, $normal ne '-99.0' ? $normal : undef;
    }

    for my $month ( 0 .. 11 ) {
        $dbh->do(q{
                INSERT INTO 'normals'
                ('Station_Number', 'Month', 'Normal')
                VALUES (?, ?, ?)
            },
            {},
            $station_number,
            sprintf('%02d', $month + 1),
            $normals[$month]
        );
    }

    my @stdevs;
    if ( defined $stdevs ) {
        (undef, $stdevs) = split /=\s+/, $stdevs;
        for my $sd ( split /\s+/, $stdevs ) {
            push @stdevs, $sd ne '-99.0' ? $sd : undef;
        }
    }
    else {
        @stdevs = (undef) x 12;
    }

    for my $month ( 0 .. 11 ) {
        $dbh->do(q{
                INSERT INTO 'stdevs'
                ('Station_Number', 'Month', 'Standard_Deviation')
                VALUES (?, ?, ?)
            },
            {},
            $station_number,
            sprintf('%02d', $month + 1),
            $stdevs[$month]
        );
    }

    my @data = split /\n/, $body;
    for my $row ( @data ) {
        my @fields = split /\s+/, $row;
        for my $month ( 1 .. 12 ) {
            my $obs = $fields[$month];
            $obs = undef if $obs eq '-99.0';
            $dbh->do(q{
                    INSERT INTO 'data'
                    ('Station_Number', 'Year', 'Month', 'Obs')
                    VALUES (?, ?, ?, ?)
                },
                {},
                $station_number,
                $fields[0],
                sprintf('%02d', $month),
                $obs,
            );
        }
    }

    $dbh->commit;
    return;
}
__DATA__
=head COPYRIGHT

Copyright (c) 2009 A. Sinan Unur http://www.unur.com/sinan/

=head LICENSE

This package is distributed under the same terms as Perl.
See http://search.cpan.org/perldoc/perlartistic

THIS PACKAGE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.