#!/usr/bin/env perl

=head1 NAME

make-junctions-database

=head1 USAGE

augment-junctions-database path/to/data.csv

=head1 DESCRIPTION

Adds to the SQLite file created by `make-junctions-database`. This requires that
script to have been run and the initial database created.

It expects a CSV with a header row: road,junction,point_x,point_y
and the data in those columns to be:

* road - name of road, eg A1
* junction - description of the point
* point_x - easting
* point_y - northing

The junction field can be either

* a junction number, e.g J12
* a junction number and a description, e.g J12 Town Name
* a junction/point descrption, e.g Town Name

Where the function field is a junction number and a description two rows will be created,
one for the junction number and one for the description.

The resulting database is used by the L<HighwaysEngland> package to lookup junctions.

=cut

use strict;
use warnings;

BEGIN {
    use File::Basename qw(dirname);
    use File::Spec;
    my $d = dirname(File::Spec->rel2abs($0));
    require "$d/../../setenv.pl";
}

use DBI;
use Text::CSV;
use FixMyStreet;
use HighwaysEngland;

my $db = DBI->connect('dbi:SQLite:dbname='. HighwaysEngland::database_file);

my $q_junction_chk = $db->prepare('SELECT road, name FROM junction WHERE road = ? AND name = ?');
my $q_junction = $db->prepare('INSERT INTO junction (road, name, easting, northing) VALUES (?, ?, ?, ?)');

my $csv = Text::CSV->new ({ binary => 1, auto_diag => 1 });
die "Usage: $0 <csv_file>\n" unless @ARGV;
open my $fh, "<:encoding(utf8)", $ARGV[0] or die "$ARGV[0]: $!";
$csv->header($fh);

my %all;
my $c = 0;
while (my $row = $csv->getline_hr($fh)) {
    my $junction = uc $row->{junction};
    my $alt_name;
    if ($junction =~ /^(J\d+)\s+(.*)$/) {
        ($junction, $alt_name) = ($1, $2);
    }

    my $road = uc $row->{road};
    next unless $road =~ /^[AM]/;

    # strip extra precision
    (my $e = $row->{point_x}) =~ s/\.\d+$//;
    (my $n = $row->{point_y}) =~ s/\.\d+$//;

    if ($alt_name) {
        $q_junction_chk->execute($road, $alt_name) or warn $db->errstr . " $road $alt_name";
        unless ($q_junction_chk->fetchrow_hashref) {
            $q_junction->execute($road, $alt_name, $e, $n) or warn $db->errstr . " $road $alt_name";
        }
    }

    $q_junction_chk->execute($road, $junction) or warn $db->errstr . " $road $junction";
    next if $q_junction_chk->fetchrow_hashref;
    $q_junction->execute($road, $junction, $e, $n) or warn $db->errstr . " $road $junction";
}

close $fh;