#!/usr/bin/env bash
# ============================================================================
# geofide-import.sh — load GeoNames dumps into initia19_geofide, ON THE SERVER.
#
# Run this on the DB server (as root), not on a workstation. It downloads the
# dumps directly over the datacenter link and LOADs from local disk, so nothing
# has to be pushed up a home connection.
#
# Usage:
#   bash geofide-import.sh lookup                       # the 6 small lookup files
#   bash geofide-import.sh geoname US                   # one country
#   bash geofide-import.sh geoname all                  # whole world (~13.5M rows)
#   bash geofide-import.sh geoname all "P,A"            # filtered to classes
#   bash geofide-import.sh geoname US "" /root/US.txt   # use an existing local file
#   bash geofide-import.sh postal all                   # postal codes
#   bash geofide-import.sh shapes                       # bulk country polygons
#   bash geofide-import.sh sync                         # daily incremental (yesterday)
#   bash geofide-import.sh sync 7                       # catch up the last 7 days
#
# Prereq — create /root/.geofide.cnf first (chmod 600):
#
#   [client]
#   user=initia19_geofider
#   password=YOUR_DB_PASSWORD
#   host=localhost
#
# Notes:
#   * Works in /root, NOT /tmp — /tmp is a small loop device on this box.
#   * --local-infile=1 is required on the client for LOAD DATA LOCAL INFILE.
#   * geoname loads via a VARCHAR staging table so empty strings become NULL
#     properly (GeoNames uses '' for "unknown").
# ============================================================================

set -euo pipefail

VERSION="2026-09-25.3"   # bump on every change — printed in the banner so a stale copy is obvious
DB=initia19_geofide
WORK=/root/geofide-import
CNF=/root/.geofide.cnf
DUMP=https://download.geonames.org/export/dump
ZIPBASE=https://download.geonames.org/export/zip

MODE="${1:-geoname}"
SCOPE="${2:-US}"
CLASSES="${3:-}"
LOCALFILE="${4:-}"

echo "geofide-import.sh v$VERSION"

[ -f "$CNF" ] || { echo "ERROR: missing $CNF — see the header of this script."; exit 1; }
mkdir -p "$WORK"
cd "$WORK"

MYSQL=(mysql --defaults-extra-file="$CNF" --local-infile=1)
stamp() { date '+%Y-%m-%d %H:%M:%S'; }
hr() { printf '%s\n' "------------------------------------------------------------"; }

# load a dump file into a table; $1=file $2=table $3=col list $4=ignore-lines
load_tsv() {
  local file="$1" table="$2" cols="$3" ignore="${4:-0}"
  "${MYSQL[@]}" "$DB" <<SQL
LOAD DATA LOCAL INFILE '$file'
  INTO TABLE \`$table\`
  FIELDS TERMINATED BY '\t' ESCAPED BY ''
  LINES TERMINATED BY '\n'
  IGNORE $ignore LINES
  ($cols);
SQL
}

case "$MODE" in

# ---------------------------------------------------------------------------
lookup)
  hr; echo "lookup files -> $WORK"; hr
  for f in admin1CodesASCII.txt admin2Codes.txt featureCodes_en.txt timeZones.txt countryInfo.txt; do
    [ -f "$f" ] || { echo "downloading $f"; curl -sS -O "$DUMP/$f"; }
  done
  [ -f hierarchy.zip ] || curl -sS -O "$DUMP/hierarchy.zip"
  [ -f hierarchy.txt ] || unzip -o -q hierarchy.zip
  grep -v '^#' countryInfo.txt > countryInfo.clean.tsv

  "${MYSQL[@]}" "$DB" -e "TRUNCATE admin1"
  load_tsv "$WORK/admin1CodesASCII.txt" admin1 "code,name,ascii_name,geonameid"
  "${MYSQL[@]}" "$DB" -e "TRUNCATE admin2"
  load_tsv "$WORK/admin2Codes.txt" admin2 "code,name,ascii_name,geonameid"
  "${MYSQL[@]}" "$DB" -e "TRUNCATE feature_code"
  load_tsv "$WORK/featureCodes_en.txt" feature_code "code,name,description"
  "${MYSQL[@]}" "$DB" -e "TRUNCATE timezone"
  load_tsv "$WORK/timeZones.txt" timezone "country_code,timezone_id,gmt_offset,dst_offset,raw_offset" 1
  "${MYSQL[@]}" "$DB" -e "TRUNCATE country"
  load_tsv "$WORK/countryInfo.clean.tsv" country "iso_code,iso3,iso_numeric,fips,country,capital,area_sqkm,population,continent,tld,currency_code,currency_name,phone,postal_format,postal_regex,languages,geonameid,neighbours,equivalent_fips"
  "${MYSQL[@]}" "$DB" -e "TRUNCATE hierarchy"
  load_tsv "$WORK/hierarchy.txt" hierarchy "parent_id,child_id,type"

  "${MYSQL[@]}" "$DB" -e "
    SELECT 'admin1' t, COUNT(*) n FROM admin1
    UNION ALL SELECT 'admin2', COUNT(*) FROM admin2
    UNION ALL SELECT 'feature_code', COUNT(*) FROM feature_code
    UNION ALL SELECT 'timezone', COUNT(*) FROM timezone
    UNION ALL SELECT 'country', COUNT(*) FROM country
    UNION ALL SELECT 'hierarchy', COUNT(*) FROM hierarchy;"
  ;;

# ---------------------------------------------------------------------------
geoname)
  hr; echo "geoname load — scope=$SCOPE classes=${CLASSES:-all}"; hr
  T0=$(date +%s)

  if [ -n "$LOCALFILE" ]; then
    TXT="$LOCALFILE"
  elif [ "${SCOPE,,}" = "all" ]; then
    [ -f allCountries.zip ] || { echo "downloading allCountries.zip (~402M)..."; curl -sS -O "$DUMP/allCountries.zip"; }
    [ -f allCountries.txt ] || { echo "unzipping..."; unzip -o -q allCountries.zip; }
    TXT="$WORK/allCountries.txt"
  else
    ISO="${SCOPE^^}"
    [ -f "$ISO.zip" ] || { echo "downloading $ISO.zip..."; curl -sS -O "$DUMP/$ISO.zip"; }
    [ -f "$ISO.txt" ] || { echo "unzipping..."; unzip -o -q "$ISO.zip"; }
    TXT="$WORK/$ISO.txt"
  fi
  [ -f "$TXT" ] || { echo "ERROR: missing $TXT"; exit 1; }

  # Guard: refuse to load a file that isn't the 19-field geoname dump. Without
  # this, a stray/overwritten file (e.g. the postal allCountries.txt) would be
  # silently loaded as places.
  FIELDS=$(head -1 "$TXT" | awk -F'\t' '{print NF}')
  if [ "$FIELDS" != "19" ]; then
    echo "ERROR: $TXT has $FIELDS tab-fields, expected 19 — refusing to load (wrong file?)."
    exit 1
  fi
  echo "$(stamp) source: $TXT ($(du -h "$TXT" | cut -f1))"

  # filter
  WHERE="WHERE geonameid REGEXP '^[0-9]+\$'"
  if [ -n "$CLASSES" ]; then
    IN=$(printf '%s' "$CLASSES" | sed "s/[^,]*/'&'/g")
    WHERE="$WHERE AND feature_class IN ($IN)"
  fi

  echo "$(stamp) creating staging table..."
  "${MYSQL[@]}" "$DB" <<'SQL'
DROP TABLE IF EXISTS geoname_stage;
CREATE TABLE geoname_stage (
  geonameid VARCHAR(20), name VARCHAR(200), asciiname VARCHAR(200), alternatenames TEXT,
  latitude VARCHAR(30), longitude VARCHAR(30), feature_class VARCHAR(5), feature_code VARCHAR(20),
  country_code VARCHAR(5), cc2 VARCHAR(200), admin1_code VARCHAR(20), admin2_code VARCHAR(80),
  admin3_code VARCHAR(20), admin4_code VARCHAR(20), population VARCHAR(20), elevation VARCHAR(20),
  dem VARCHAR(20), timezone VARCHAR(60), modification_date VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL

  echo "$(stamp) staging $(basename "$TXT") — this is the slow part..."
  load_tsv "$TXT" geoname_stage "geonameid,name,asciiname,alternatenames,latitude,longitude,feature_class,feature_code,country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,dem,timezone,modification_date"
  STAGED=$("${MYSQL[@]}" -N -B "$DB" -e "SELECT COUNT(*) FROM geoname_stage")
  echo "$(stamp) staged $STAGED rows in $(( $(date +%s) - T0 ))s"

  echo "$(stamp) converting into geoname..."
  "${MYSQL[@]}" "$DB" <<SQL
INSERT INTO geoname
  (geonameid,name,asciiname,alternatenames,latitude,longitude,feature_class,feature_code,
   country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,
   dem,timezone,modification_date)
SELECT CAST(geonameid AS UNSIGNED), name, asciiname, NULLIF(alternatenames,''),
  CAST(latitude AS DECIMAL(10,7)), CAST(longitude AS DECIMAL(10,7)),
  feature_class, feature_code, country_code, NULLIF(cc2,''),
  NULLIF(admin1_code,''), NULLIF(admin2_code,''), NULLIF(admin3_code,''), NULLIF(admin4_code,''),
  CAST(NULLIF(population,'') AS UNSIGNED),
  CAST(NULLIF(elevation,'') AS SIGNED), CAST(NULLIF(dem,'') AS SIGNED),
  NULLIF(timezone,''), NULLIF(modification_date,'')
FROM geoname_stage
$WHERE;
DROP TABLE geoname_stage;
INSERT INTO import_log (dataset, scope, rows_loaded, started_at, finished_at, note)
  VALUES ('geoname', '$SCOPE', $STAGED, FROM_UNIXTIME($T0), NOW(), '${CLASSES:+classes=$CLASSES}');
SQL

  "${MYSQL[@]}" "$DB" -e "SELECT COUNT(*) AS geoname_rows FROM geoname;
    SELECT table_name, ROUND((data_length+index_length)/1024/1024,1) AS mb
    FROM information_schema.TABLES WHERE table_schema='$DB' AND table_name='geoname';"
  echo "$(stamp) total elapsed: $(( $(date +%s) - T0 ))s"
  ;;

# ---------------------------------------------------------------------------
postal)
  hr; echo "postal codes — scope=$SCOPE"; hr
  # IMPORTANT: the postal zips also contain a member literally named
  # allCountries.txt / XX.txt — the same names as the geoname dumps. Unzip into
  # a dedicated subdirectory so they can never clobber the gazetteer dumps.
  PDIR="$WORK/postal"
  mkdir -p "$PDIR"
  if [ "${SCOPE,,}" = "all" ]; then
    [ -f "$PDIR/allCountries.zip" ] || curl -sS -o "$PDIR/allCountries.zip" "$ZIPBASE/allCountries.zip"
    [ -f "$PDIR/allCountries.txt" ] || unzip -o -q "$PDIR/allCountries.zip" -d "$PDIR"
    TXT="$PDIR/allCountries.txt"
  else
    ISO="${SCOPE^^}"
    [ -f "$PDIR/$ISO.zip" ] || curl -sS -o "$PDIR/$ISO.zip" "$ZIPBASE/$ISO.zip"
    [ -f "$PDIR/$ISO.txt" ] || unzip -o -q "$PDIR/$ISO.zip" -d "$PDIR"
    TXT="$PDIR/$ISO.txt"
  fi
  [ -f "$TXT" ] || { echo "ERROR: missing $TXT"; exit 1; }

  # Guard: postal files have 12 fields; refuse anything else.
  FIELDS=$(head -1 "$TXT" | awk -F'\t' '{print NF}')
  if [ "$FIELDS" != "12" ]; then
    echo "ERROR: $TXT has $FIELDS tab-fields, expected 12 — refusing to load."
    exit 1
  fi
  echo "$(stamp) source: $TXT ($(du -h "$TXT" | cut -f1))"
  load_tsv "$TXT" postal_code "country_code,postal_code,place_name,admin1_name,admin1_code,admin2_name,admin2_code,admin3_name,admin3_code,latitude,longitude,accuracy"
  "${MYSQL[@]}" "$DB" -e "SELECT COUNT(*) AS postal_rows FROM postal_code;"
  ;;

# ---------------------------------------------------------------------------
shapes)
  hr; echo "bulk country shapes (247 country polygons)"; hr
  [ -f shapes_simplified_low.json.zip ] || curl -sS -O "$DUMP/shapes_simplified_low.json.zip"
  [ -f shapes_simplified_low.json ] || unzip -o -q shapes_simplified_low.json.zip
  echo "$(stamp) downloaded: $WORK/shapes_simplified_low.json ($(du -h "$WORK/shapes_simplified_low.json" | cut -f1))"
  echo
  echo "This file is small (~4MB) — storing it needs JSON parsing, so run the"
  echo "workstation importer (it fetches the same file and inserts the 247 rows):"
  echo "    php import/import.php shapes"
  ;;

# ---------------------------------------------------------------------------
# Daily incremental sync. GeoNames publishes, for each day:
#   modifications-YYYY-MM-DD.txt   changed records, same 19 fields as the dump
#   deletes-YYYY-MM-DD.txt         geonameid <tab> name <tab> comment
# They are only kept for a few days, so this needs to run daily (cron).
#
#   bash geofide-import.sh sync        # yesterday
#   bash geofide-import.sh sync 7      # catch up the last 7 days
sync)
  DAYS="${2:-1}"
  hr; echo "sync — last $DAYS day(s)"; hr
  mkdir -p "$WORK/sync"
  T0=$(date +%s)
  APPLIED=0

  for i in $(seq 1 "$DAYS"); do
    D=$(date -d "-$i day" +%F)
    MOD="$WORK/sync/modifications-$D.txt"
    DEL="$WORK/sync/deletes-$D.txt"

    [ -s "$MOD" ] || { curl -sS -f -o "$MOD" "$DUMP/modifications-$D.txt" || rm -f "$MOD"; }
    [ -s "$DEL" ] || { curl -sS -f -o "$DEL" "$DUMP/deletes-$D.txt"      || rm -f "$DEL"; }

    # ── modifications ──────────────────────────────────────────────────────
    if [ -s "$MOD" ]; then
      F=$(head -1 "$MOD" | awk -F'\t' '{print NF}')
      if [ "$F" = "19" ]; then
        echo "$(stamp) $D  modifications: $(wc -l < "$MOD") rows"
        "${MYSQL[@]}" "$DB" <<'SQL'
DROP TABLE IF EXISTS sync_stage;
CREATE TABLE sync_stage (
  geonameid VARCHAR(20), name VARCHAR(200), asciiname VARCHAR(200), alternatenames TEXT,
  latitude VARCHAR(30), longitude VARCHAR(30), feature_class VARCHAR(5), feature_code VARCHAR(20),
  country_code VARCHAR(5), cc2 VARCHAR(200), admin1_code VARCHAR(20), admin2_code VARCHAR(80),
  admin3_code VARCHAR(20), admin4_code VARCHAR(20), population VARCHAR(20), elevation VARCHAR(20),
  dem VARCHAR(20), timezone VARCHAR(60), modification_date VARCHAR(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL
        load_tsv "$MOD" sync_stage "geonameid,name,asciiname,alternatenames,latitude,longitude,feature_class,feature_code,country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,dem,timezone,modification_date"
        "${MYSQL[@]}" "$DB" <<'SQL'
INSERT INTO geoname
  (geonameid,name,asciiname,alternatenames,latitude,longitude,feature_class,feature_code,
   country_code,cc2,admin1_code,admin2_code,admin3_code,admin4_code,population,elevation,
   dem,timezone,modification_date)
SELECT CAST(geonameid AS UNSIGNED), name, asciiname, NULLIF(alternatenames,''),
  CAST(latitude AS DECIMAL(10,7)), CAST(longitude AS DECIMAL(10,7)),
  feature_class, feature_code, country_code, NULLIF(cc2,''),
  NULLIF(admin1_code,''), NULLIF(admin2_code,''), NULLIF(admin3_code,''), NULLIF(admin4_code,''),
  CAST(NULLIF(population,'') AS UNSIGNED),
  CAST(NULLIF(elevation,'') AS SIGNED), CAST(NULLIF(dem,'') AS SIGNED),
  NULLIF(timezone,''), NULLIF(modification_date,'')
FROM sync_stage
WHERE geonameid REGEXP '^[0-9]+$'
ON DUPLICATE KEY UPDATE
  name=VALUES(name), asciiname=VALUES(asciiname), alternatenames=VALUES(alternatenames),
  latitude=VALUES(latitude), longitude=VALUES(longitude),
  feature_class=VALUES(feature_class), feature_code=VALUES(feature_code),
  country_code=VALUES(country_code), cc2=VALUES(cc2),
  admin1_code=VALUES(admin1_code), admin2_code=VALUES(admin2_code),
  admin3_code=VALUES(admin3_code), admin4_code=VALUES(admin4_code),
  population=VALUES(population), elevation=VALUES(elevation), dem=VALUES(dem),
  timezone=VALUES(timezone), modification_date=VALUES(modification_date);
DROP TABLE sync_stage;
SQL
        APPLIED=$((APPLIED + 1))
      else
        echo "$(stamp) $D  modifications: unexpected field count ($F) — skipped"
      fi
    else
      echo "$(stamp) $D  modifications: not published (GeoNames keeps only a few days)"
    fi

    # ── deletes ────────────────────────────────────────────────────────────
    if [ -s "$DEL" ]; then
      echo "$(stamp) $D  deletes: $(wc -l < "$DEL") rows"
      "${MYSQL[@]}" "$DB" <<'SQL'
DROP TABLE IF EXISTS sync_deletes;
CREATE TABLE sync_deletes (
  geonameid VARCHAR(20), name VARCHAR(200), note VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
SQL
      load_tsv "$DEL" sync_deletes "geonameid,name,note"
      "${MYSQL[@]}" "$DB" <<'SQL'
DELETE g FROM geoname g JOIN sync_deletes d ON d.geonameid = g.geonameid;
DELETE s FROM shape   s JOIN sync_deletes d ON d.geonameid = s.geonameid;
DROP TABLE sync_deletes;
SQL
      APPLIED=$((APPLIED + 1))
    fi
  done

  "${MYSQL[@]}" "$DB" -e "
    INSERT INTO import_log (dataset, scope, rows_loaded, started_at, finished_at, note)
      VALUES ('sync', '$DAYS day(s)', NULL, FROM_UNIXTIME($T0), NOW(), 'applied=$APPLIED');
    SELECT COUNT(*) AS geoname_rows FROM geoname;"
  echo "$(stamp) applied $APPLIED file(s) in $(( $(date +%s) - T0 ))s"
  ;;

*)
  sed -n '9,18p' "$0" | sed 's/^# \{0,1\}//'
  exit 2
  ;;
esac

hr; echo "$(stamp) done."; hr