#!/usr/bin/env perl
# bi-geocode -- geocode-cache warming CLI for Database::BI
#
# Opens a dataset spec, geocodes every distinct value of the named column
# (or of several, --col repeated, joined with ", ": an address, then its country),
# with --text added to the end of each place ("Virginia, USA"),
# and writes the resolved coordinates into the shared geocode cache in
# models.json.  Subsequent map views for the same dataset serve from the
# cache without making any geocoder requests.
#
# Places are looked up in Geo::Coder::Free's local data first, then on
# OpenStreetMap (Nominatim) at one a second, with no limit on how many: the
# map page asks Nominatim for at most map_geocode_online_max places, and this
# script is how the rest are found.  --offline keeps every lookup local.
#
# The script is idempotent: a second run against the same column with a
# warm cache resolves zero additional places (and says so).
#
# Usage:
#   bi-geocode --spec <l-spec> --col <column> [--col <column> ...]
#              [--text <text>] [--data-dir <dir>] [--model-dir <dir>]
#              [--openaddr <path>] [--region <CC>]
#              [--offline] [--online-max <n>]
#              [--dry-run] [--verbose]
#
# Exit codes:  0 = ok
#              1 = column not found or no records loaded
#              2 = bad arguments / data source unavailable
#
# Suitable for cron:
#   0 2 * * * cd /opt/myapp && ./script/bi-geocode \
#       --spec table:customers --col city --region US --verbose

use strict;
use warnings;

use FindBin qw($Bin);
use lib "$Bin/../lib";

use Getopt::Long qw(GetOptions :config no_ignore_case);
use File::Spec;
use Mojo::JSON qw(decode_json encode_json);
use Mojo::File;
use Database::BI::Util qw(iso8601_utc);
use Database::BI::I18N ();

# Messages in the language the locale names: LANG=en_US.UTF-8 is American
# English, anything else British (CGI::Lingua chooses).
$Database::BI::I18N::LANGUAGE = Database::BI::I18N::from_locale();

# ---------------------------------------------------------------------------
# Parse arguments
# ---------------------------------------------------------------------------

my @opt_cols;
my ($opt_spec, $opt_data_dir, $opt_model_dir,
	$opt_openaddr, $opt_region, $opt_dry_run, $opt_verbose, $opt_help,
	$opt_offline, $opt_online_max, $opt_text);

GetOptions(
	'spec|s=s'       => \$opt_spec,
	'col|c=s'        => \@opt_cols,
	'text|t=s'       => \$opt_text,
	'data-dir|d=s'   => \$opt_data_dir,
	'model-dir|m=s'  => \$opt_model_dir,
	'openaddr|o=s'   => \$opt_openaddr,
	'region|r=s'     => \$opt_region,
	'offline'        => \$opt_offline,
	'online-max=i'   => \$opt_online_max,
	'dry-run|n'      => \$opt_dry_run,
	'verbose|v'      => \$opt_verbose,
	'help|h'         => \$opt_help,
) or do { _usage(); exit 2 };

if ($opt_help) { _usage(); exit 0 }

if (defined($opt_online_max) && $opt_online_max < 1) {
	warn "bi-geocode: --online-max must be a positive whole number\n";
	exit 2;
}

unless (defined $opt_spec && length $opt_spec) {
	warn "bi-geocode: --spec <l-spec> is required\n";
	exit 2;
}
@opt_cols = grep { length } @opt_cols;
unless (@opt_cols) {
	warn "bi-geocode: --col <column> is required\n";
	exit 2;
}

require Database::BI::Model::Geocode;

# The same tidying as the map page's "Add to each place" (loc_text=), so the
# places cached here are the ones the map asks for.
my ($place_text) = Database::BI::Model::Geocode->place_text($opt_text);
unless (defined $place_text) {
	no warnings qw(once);
	warn "bi-geocode: --text is added to the end of each place: one line of at most ",
		$Database::BI::Model::Geocode::PLACE_TEXT_MAX, " characters\n";
	exit 2;
}

my $data_dir  = $opt_data_dir  // 'data';
my $model_dir = $opt_model_dir // '.bi';

# ---------------------------------------------------------------------------
# Load the data source
# ---------------------------------------------------------------------------

require Database::BI::Model::DataSource;

# table:<name>/<table> and path:<file>#<table> name one table of a file.
my ($table, $path, $url, $internal);
if ($opt_spec =~ /\Atable:(.+)\z/) {
	$table = $1;
	($table, $internal) = ($1, $2) if $table =~ m{\A([^/]+)/(.+)\z};
} elsif ($opt_spec =~ /\Apath:(.+)\z/) {
	require Database::BI::Model::DataSource;
	($path, $internal) = Database::BI::Model::DataSource::split_path($1);
} elsif ($opt_spec =~ /\Aurl:(.+)\z/) {
	$url = $1;
} else {
	warn "bi-geocode: spec must start with table:, path:, or url:\n";
	exit 2;
}

my $source = eval {
	if (defined $table) {
		Database::BI::Model::DataSource->new(
			table     => $table,
			directory => $data_dir,
			defined $internal ? (internal_table => $internal) : (),
		);
	} elsif (defined $path) {
		# As the map page opens it: the table is the file's name without its
		# extension, in the directory the file really is in.
		my $f = Mojo::File->new($path)->realpath;
		(my $stem = $f->basename) =~ s/\.[^.]+\z//;
		Database::BI::Model::DataSource->new(
			table     => $stem,
			directory => $f->dirname->to_string,
			defined $internal ? (internal_table => $internal) : (),
		);
	} else {
		Database::BI::Model::DataSource->new(url => $url);
	}
};
if ($@ || !defined $source) {
	(my $e = $@ // 'unknown error') =~ s/ at \S+ line \d+\.?\n?$//;
	warn "bi-geocode: could not open data source: $e\n";
	exit 2;
}

my $records = eval { $source->fetch_all };
if ($@ || !defined $records || !@$records) {
	(my $e = $@ // '') =~ s/ at \S+ line \d+\.?\n?$//;
	warn "bi-geocode: no records loaded" . ($e ? ": $e" : '') . "\n";
	exit 1;
}

# Validate the columns
my $columns = $source->columns // [ keys %{ $records->[0] } ];
my %col_set = map { $_ => 1 } @$columns;
for my $col (@opt_cols) {
	next if $col_set{$col};
	warn "bi-geocode: column \"$col\" not found in dataset\n";
	warn "  available: " . join(', ', sort keys %col_set) . "\n";
	exit 1;
}

_log("Loaded %d records from %s", scalar @$records, $opt_spec);

# ---------------------------------------------------------------------------
# Load models / geocode cache
# ---------------------------------------------------------------------------

my $models_file = Mojo::File->new($model_dir, 'models.json');
my $models = {};
if (-f $models_file) {
	eval { $models = decode_json($models_file->slurp) };
	warn "bi-geocode: could not read models.json: $@\n" if $@;
}

# Several columns are one place, their cells joined with ", ", then --text, as
# on the map page.
my ($places, $opt_col) = Database::BI::Model::Geocode->place_rows($records, \@opt_cols, $place_text);
_log("Each place ends \", %s\"", $place_text) if length $place_text;

my $cache_spec = $opt_spec . '|loc=' . $opt_col;
my $legacy  = Database::BI::Model::Geocode->cache_from_models($models, $cache_spec);
my $shared  = Database::BI::Model::Geocode->shared_cache_from_models($models);
my %cache   = (%$legacy, %$shared);

my %args;
$args{openaddr}   = $opt_openaddr if defined $opt_openaddr && length $opt_openaddr;
# The same Geo::Coder::List cache the map page uses: Geocode->new chooses its
# directory (Object::Configure, CACHEDIR, CACHE_DIR, cache_dir in
# database_bi.conf, then <model-dir>/geocode-cache).  With --dry-run nothing is
# kept, the chain's cache included.
if ($opt_dry_run) {
	$args{cache} = {};
} else {
	Mojo::File->new($model_dir)->make_path;
	$args{model_dir} = $model_dir;
}
$args{online}     = $opt_offline ? 0 : 1;
$args{online_max} = $opt_online_max if defined $opt_online_max;

my $geocode = Database::BI::Model::Geocode->new(%args);

my %opts;
$opts{cache} = \%cache;
# No cap for the warming script: every row could name a different place, so
# the number of rows is a cap nothing reaches.  (Geocode refuses max => 0.)
$opts{max}   = scalar(@$records) || 1;
if (defined $opt_region && $opt_region =~ /\A[A-Za-z]{2}\z/) {
	$opts{region} = $opt_region;
}

# ---------------------------------------------------------------------------
# Resolve
# ---------------------------------------------------------------------------

my $t0 = time;
my $result = $geocode->resolve_column($places, $opt_col, %opts);
my $elapsed = time - $t0;

my $counts   = $result->{counts};
my $messages = $result->{messages};

_log("Resolved %d/%d distinct places in %ds",
	$counts->{resolved} // 0, $counts->{distinct} // 0, $elapsed);
_log("  cache hits: %d  new: %d  of them online: %d  unresolved: %d  left for later: %d",
	$counts->{cached} // 0, $counts->{attempted} // 0, $counts->{online} // 0,
	$counts->{unresolved} // 0, $counts->{not_attempted} // 0);

for my $msg (@$messages) {
	_log("  [%s] %s", $msg->{key} // 'info', $msg->{text} // '');
}

# ---------------------------------------------------------------------------
# Persist
# ---------------------------------------------------------------------------

if ($opt_dry_run) {
	_log("Dry run: cache not written.");
	exit 0;
}

if ($counts->{attempted} // 0) {
	my $backend = $result->{backend};
	my $updated = Database::BI::Model::Geocode->models_with_shared_cache(
		$models, $result->{cache},
		(defined($backend) ? (backend => $backend) : ()),
		built => iso8601_utc(),
	);

	eval {
		Mojo::File->new($model_dir)->make_path;
		my $tmp = Mojo::File->new($models_file . '.tmp');
		$tmp->spurt(encode_json($updated));
		rename $tmp->to_string, $models_file->to_string
			or die "rename failed: $!\n";
	};
	if ($@) {
		(my $e = $@) =~ s/ at \S+ line \d+\.?\n?$//;
		warn "bi-geocode: could not write models.json: $e\n";
		exit 2;
	}
	_log("Cache written to %s", $models_file->to_string);
} else {
	_log("Nothing new to write (all places already cached).");
}

exit 0;

# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

sub _log {
	return unless $opt_verbose;
	my ($fmt, @args) = @_;
	printf STDERR "$fmt\n", @args;
}

sub _usage {
	print STDERR <<'USAGE';
Usage: bi-geocode --spec <l-spec> --col <column> [--col <column> ...] [options]

Required:
  --spec, -s <spec>     Dataset spec: table:<name>, path:</abs/path>, url:<url>
  --col,  -c <column>   Column holding place names to geocode; repeat it for a
                        place across columns, joined with ", " in that order
                        (--col Address --col Country)

Options:
  --text,      -t <text>  Text added to the end of each place, after ", ", as
                          the map's "Add to each place" (--text 'Virginia, USA')
  --data-dir,  -d <dir>   Data directory (default: data)
  --model-dir, -m <dir>   Model store directory (default: .bi)
  --openaddr,  -o <path>  Path to an OpenAddresses data directory for Geo::Coder::Free
  --region,    -r <CC>    ISO 3166-1 alpha-2 region hint (e.g. US, GB)
  --offline               Look places up in the local data only, never on
                          OpenStreetMap
  --online-max <n>        Look up at most <n> places on OpenStreetMap
                          (default: no limit; it is asked once a second)
  --dry-run,   -n         Resolve but do not write the cache
  --verbose,   -v         Print progress to STDERR
  --help,      -h         Show this message

Exit codes:
  0 = success
  1 = column not found or no records
  2 = bad arguments or data source error
USAGE
}

__END__

=head1 NAME

bi-geocode - fill Database::BI's geocode cache for a column of places

=head1 SYNOPSIS

    bi-geocode --spec <spec> --col <column> [--col <column> ...]
               [--text <text>] [--data-dir <dir>] [--model-dir <dir>]
               [--openaddr <path>] [--region <CC>]
               [--offline] [--online-max <n>]
               [--dry-run] [--verbose]

=head1 DESCRIPTION

Opens a dataset, looks up every distinct value of the named column (or of
several, C<--col> repeated, joined with C<", ">: an address, then its country),
with C<--text> added to the end of each place, and writes the coordinates into the shared geocode cache in the model store.
Map pages for the dataset then draw from the cache without asking a geocoder.

Places are looked up in L<Geo::Coder::Free>'s local data first, then on
OpenStreetMap (Nominatim) at one a second, with no limit unless
C<--online-max> sets one: the map page asks Nominatim for at most
C<map_geocode_online_max> places, and this is how the rest are found.
C<--offline> keeps every lookup local.  A second run on a warm cache looks up
nothing more, and says so.  Geocoding needs Geo::Coder::Free and
Geo::Coder::List, which are recommended rather than required.

    0 2 * * * cd /opt/myapp && bi-geocode --spec table:customers --col city --region US

=head1 OPTIONS

=over 4

=item B<--spec>, B<-s> I<spec>

The dataset: C<table:name>, C<path:/abs/path> or C<url:...>.  Required.

=item B<--col>, B<-c> I<column>

A column holding place names.  Required; repeat it for a place spread across
columns (C<--col Address --col Country>).

=item B<--text>, B<-t> I<text>

Text added to the end of each place, after C<", ">, for places the cells do
not name fully: C<--col County --text 'Virginia, USA'> looks C<Accomack> up as
C<Accomack, Virginia, USA>.  It is the map panel's I<Add to each place>
(C<loc_text=>), tidied the same way (spaces trimmed, a leading comma dropped),
so the map finds what this caches.  A row with no place is not given the text.
At most 200 characters, on one line.

=item B<--data-dir>, B<-d> I<dir>

The data folder (default: F<data>).

=item B<--model-dir>, B<-m> I<dir>

The model store folder (default: F<.bi>).  Places OpenStreetMap found are
kept in F<E<lt>model-dirE<gt>/geocode-cache> unless another cache directory is
set: C<cache_dir> through L<Object::Configure> (or the environment variable
C<Database__BI__Model__Geocode__cache_dir>), then C<$ENV{CACHEDIR}>, then
C<$ENV{CACHE_DIR}>, then C<cache_dir> in F<database_bi.conf> (the file
C<MOJO_CONFIG> names, else the one in the current folder).

=item B<--openaddr>, B<-o> I<path>

An OpenAddresses data folder for Geo::Coder::Free.

=item B<--region>, B<-r> I<CC>

An ISO 3166-1 alpha-2 region hint, such as C<US> or C<GB>.

=item B<--offline>

Use the local data only, never OpenStreetMap.

=item B<--online-max> I<n>

Look up at most I<n> places on OpenStreetMap.

=item B<--dry-run>, B<-n>

Look places up but do not write the cache.

=item B<--verbose>, B<-v>

Print progress to STDERR.

=item B<--help>, B<-h>

Show the usage.

=back

=head1 EXIT STATUS

0 on success, 1 when the column is not found or there are no rows, 2 for bad
arguments or a data source that cannot be read.

=head1 AUTHOR

Nigel Horne C<< <njh@nigelhorne.com> >>

=head1 LICENCE AND COPYRIGHT

Copyright 2026 Nigel Horne.

Usage is subject to the GPL2 licence terms.
If you use it,
please let me know.

=cut
