tas_councils/web/backfill_pid_title.php

<?php
// tools/backfill_pid_title.php
// Backfill PID + land title id for rows that already have lat/lng.
// Works in CLI and via browser (query params).

declare(strict_types=1);

// ---------- runtime mode ----------
$isCli = (PHP_SAPI === 'cli');

// Define STDOUT/STDERR when running via web
if (!$isCli) {
  if (!defined('STDOUT')) define('STDOUT', fopen('php://output', 'w'));
  if (!defined('STDERR')) define('STDERR', fopen('php://output', 'w'));
  @header('Content-Type: text/plain; charset=utf-8');
  @set_time_limit(0);
}

// ---------- config / env ----------
$DB_HOST = getenv('MYSQL_HOST') ?: 'db';
$DB_NAME = getenv('MYSQL_DATABASE') ?: 'councils';
$DB_USER = getenv('MYSQL_USER') ?: 'root';
$DB_PASS = getenv('MYSQL_PASSWORD') ?: getenv('MYSQL_ROOT_PASSWORD');
$DB_PORT = (int)(getenv('MYSQL_PORT') ?: 3306);

// Where to call list_lookup.php
$LOOKUP_URL = getenv('LOOKUP_URL') ?: 'https://modulosdesign.com.au/internal/classes/list_lookup.php';

// Throttle between requests (microseconds)
$THROTTLE_US = (int)(getenv('LOOKUP_THROTTLE_US') ?: 150000); // 150 ms

// ---------- args ----------
if ($isCli) {
  $opt = getopt('', ['table::','all','limit::','dry-run','lookup::']);
} else {
  $opt = [
    'table'   => $_GET['table']   ?? null,
    'all'     => isset($_GET['all']) ? '1' : null,
    'limit'   => $_GET['limit']   ?? null,
    'dry-run' => isset($_GET['dry_run']) ? '1' : null,
    'lookup'  => $_GET['lookup']  ?? null,
  ];
}

if (!empty($opt['lookup'])) $LOOKUP_URL = (string)$opt['lookup'];

$tableFilter = $opt['table'] ?? null;
$doAll       = isset($opt['all']) || !$tableFilter;
$limit       = isset($opt['limit']) ? max(1,(int)$opt['limit']) : 500;
$dryRun      = isset($opt['dry-run']);

// ---------- db connect ----------
$dsn = "mysql:host={$DB_HOST};port={$DB_PORT};dbname={$DB_NAME};charset=utf8mb4";
$pdo = new PDO($dsn, $DB_USER, $DB_PASS, [
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

// ---------- helpers ----------
function info($s){ fwrite(STDOUT, $s . PHP_EOL); }
function warnln($s){ fwrite(STDERR, "[WARN] $s" . PHP_EOL); }

function table_has_cols(PDO $pdo, string $t, array $cols): array {
  $have = [];
  $st = $pdo->query("SHOW COLUMNS FROM `{$t}`");
  while ($r = $st->fetch()) $have[strtolower($r['Field'])] = true;
  $out = [];
  foreach ($cols as $c) $out[$c] = isset($have[strtolower($c)]);
  return $out;
}

function ensure_cols(PDO $pdo, string $t){
  // Use your live schema names: property_id + title_reference (+ optional areas)
  $need = table_has_cols($pdo, $t, ['property_id','title_reference','area_sqm','area_ha']);
  $sqls = [];
  if (!$need['property_id'])     $sqls[] = "ALTER TABLE `{$t}` ADD COLUMN property_id VARCHAR(32) NULL";
  if (!$need['title_reference']) $sqls[] = "ALTER TABLE `{$t}` ADD COLUMN title_reference VARCHAR(64) NULL";
  if (!$need['area_sqm'])        $sqls[] = "ALTER TABLE `{$t}` ADD COLUMN area_sqm DOUBLE NULL";
  if (!$need['area_ha'])         $sqls[] = "ALTER TABLE `{$t}` ADD COLUMN area_ha DOUBLE NULL";
  foreach ($sqls as $sql) { $pdo->exec($sql); }
}

function post_json(string $url, array $payload, int $timeout=15): array {
  $ch = curl_init($url);
  $body = json_encode($payload, JSON_UNESCAPED_SLASHES|JSON_UNESCAPED_UNICODE);
  curl_setopt_array($ch, [
    CURLOPT_POST           => true,
    CURLOPT_HTTPHEADER     => ['Content-Type: application/json'],
    CURLOPT_POSTFIELDS     => $body,
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_CONNECTTIMEOUT => $timeout,
    CURLOPT_TIMEOUT        => $timeout,
	CURLOPT_HTTP_VERSION   => CURL_HTTP_VERSION_1_1,
  ]);
  $resp = curl_exec($ch);
  if ($resp === false) {
    $err = curl_error($ch); curl_close($ch);
    throw new RuntimeException("cURL error: $err");
  }
  $code = curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
  curl_close($ch);
  if ($code < 200 || $code >= 300) throw new RuntimeException("HTTP $code from lookup");
  $j = json_decode($resp, true);
  if (!is_array($j)) throw new RuntimeException("Bad JSON from lookup");
  return $j;
}

// ---------- pick tables ----------
$tables = [];
if ($doAll) {
  $st = $pdo->query("SHOW TABLES");
  while ($r = $st->fetch(PDO::FETCH_NUM)) {
    if (strpos($r[0], 'da_') === 0) $tables[] = $r[0];
  }
} else {
  $tables = [$tableFilter];
}
if (!$tables) { info("No tables."); exit(0); }

// ---------- process ----------
foreach ($tables as $t) {
  info("Table: {$t}");

  // Needs lat/lng
  $has = table_has_cols($pdo, $t, ['lat','lng']);
  if (!$has['lat'] || !$has['lng']) { info("  skip (no lat/lng)"); continue; }

  ensure_cols($pdo, $t);

  // Rows that need fill (lat/lng present; missing property_id or title_reference)
  $sql = "
    SELECT id, lat, lng, property_id, title_reference
    FROM `{$t}`
    WHERE lat IS NOT NULL AND lng IS NOT NULL
      AND (title_reference IS NULL OR title_reference = '')
    ORDER BY id ASC
    LIMIT {$limit}";
  $todo = $pdo->query($sql)->fetchAll();

  if (!$todo) { info("  nothing to do"); continue; }
  info("  found ".count($todo)." row(s) to backfill (limit {$limit})");

  $upd = $pdo->prepare("
    UPDATE `{$t}`
       SET property_id = ?, title_reference = ?, area_sqm = ?, area_ha = ?
     WHERE id = ?");

  foreach ($todo as $r) {
    $id  = (int)$r['id'];
    $lat = (float)$r['lat'];
    $lng = (float)$r['lng'];

    try {
      $resp = post_json($LOOKUP_URL, ['lat'=>$lat,'lng'=>$lng]);
      if (empty($resp['ok'])) throw new RuntimeException($resp['error'] ?? 'lookup failed');

      $pid      = isset($resp['pid']) ? preg_replace('/[^0-9]/','', (string)$resp['pid']) : null;
      $titleId  = $resp['title_id'] ?? null;
      $areaSqm  = isset($resp['area_sqm']) ? (float)$resp['area_sqm'] : null;
      $areaHa   = isset($resp['area_ha'])  ? (float)$resp['area_ha']  : null;

      if ($dryRun) {
        info(sprintf("   id=%d lat=%.6f lng=%.6f  -> pid=%s title=%s area=%.1f sqm",
          $id, $lat, $lng, ($pid?:'NULL'), ($titleId?:'NULL'), ($areaSqm?:0)));
      } else {
        $upd->execute([$pid, $titleId, $areaSqm, $areaHa, $id]);
      }
    } catch (Throwable $e) {
      warnln("   id={$id} error: ".$e->getMessage());
    }

    if ($THROTTLE_US > 0) usleep($THROTTLE_US);
  }

  info("  done {$t}");
}

info("All done.");