#!/usr/bin/env perl
# bi-geocode -- Phase 6 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),
# 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> ...]
#              [--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);

# ---------------------------------------------------------------------------
# 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);

GetOptions(
	'spec|s=s'       => \$opt_spec,
	'col|c=s'        => \@opt_cols,
	'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;
}

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 $@;
}

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

# Several columns are one place, their cells joined with ", ", as on the map page.
my ($places, $opt_col) = Database::BI::Model::Geocode->place_rows($records, \@opt_cols);

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, under the model directory.
# With --dry-run nothing is kept, the chain's cache included.
if (!$opt_dry_run) {
	Mojo::File->new($model_dir)->make_path;
	$args{cache} = Database::BI::Model::Geocode->file_cache($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:
  --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> ...]
               [--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),
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<--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>).

=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
