kithkin/app/Jobs/GeocodeEventLocations.php

<?php

namespace App\Jobs;

use App\Models\EventMeta;
use App\Models\Location;
use App\Services\Location\Geocoder;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Str;
use Illuminate\Support\Facades\Log;

class GeocodeEventLocations implements ShouldQueue
{
    use Dispatchable, Queueable, InteractsWithQueue, SerializesModels;

    // process up to n records this run; null = no cap
    public function __construct(
        public ?int $limit = null
    ) {}

    // convenience helpers
    public static function push(?int $limit = null): void {
        dispatch(new static($limit));
    }
    public static function runNow(?int $limit = null): void {
        dispatch_sync(new static($limit));
    }

    // handle geocoding
    public function handle(Geocoder $geocoder): void
    {
        // working counters
        $processed = 0;
        $created   = 0;
        $updated   = 0;
        $skipped   = 0;
        $failed    = 0;

        Log::info('GeocodeEventLocations: start', ['limit' => $this->limit]);

        // events that have a non-empty location string but no linked location row yet
        $todo = EventMeta::query()
            ->whereNull('location_id')
            ->whereNotNull('location')
            ->where('location', '<>', '')
            ->orderBy('event_id'); // important for chunkById

        $stop = false;

        // log total to process (before limit)
        $total = (clone $todo)->count();
        Log::info('[geo] starting GeocodeEventLocations', ['total' => $total, 'limit' => $this->limit]);

        // chunk through event_meta rows
        $todo->chunkById(200, function ($chunk) use ($geocoder, &$processed, &$created, &$updated, &$skipped, &$failed, &$stop) {
            foreach ($chunk as $meta) {
                if ($stop) {
                    return false; // stop further chunking
                }

                // respect limit if provided
                if ($this->limit !== null && $processed >= $this->limit) {
                    $stop = true;
                    return false;
                }

                try {
                    // geocode the free-form location string
                    $norm = $geocoder->forward($meta->location);

                    // skip obvious non-address labels or unresolved queries
                    if (!$norm || (!$norm['lat'] && !$norm['street'])) {
                        $skipped++;
                        $processed++;
                        continue;
                    }

                    // normalized match key to reduce duplicates
                    $lookup = [
                        'display_name' => $norm['display_name'],
                        'street'       => $norm['street'],
                        'city'         => $norm['city'],
                        'state'        => $norm['state'],
                        'postal'       => $norm['postal'],
                        'country'      => $norm['country'],
                    ];

                    // try to match an existing location (by normalized fields)
                    $existing = Location::where($lookup)->first();

                    // fall back to raw string match against any pre-seeded label rows
                    if (!$existing && $meta->location) {
                        $existing = Location::where('display_name', $meta->location)
                            ->orWhere('raw_address', $meta->location)
                            ->first();
                    }

                    // reuse existing location or create a new one with coords
                    $loc = $existing ?? Location::firstOrCreate(
                        $lookup,
                        [
                            'raw_address' => $norm['raw_address'],
                            'lat'         => $norm['lat'],
                            'lon'         => $norm['lon'],
                        ]
                    );

                    // if we matched an existing row missing coords, backfill once
                    if ($existing && (is_null($existing->lat) || is_null($existing->lon))) {
                        $existing->lat = $norm['lat'];
                        $existing->lon = $norm['lon'];
                        $existing->raw_address ??= $norm['raw_address'];
                        $existing->save();
                        $updated++;
                    }

                    if ($loc->wasRecentlyCreated) {
                        $created++;
                    }

                    // link event_meta → locations
                    $meta->location_id = $loc->id;
                    $meta->save();

                    $processed++;

                } catch (\Throwable $e) {
                    $failed++;
                    $processed++;
                    Log::warning('GeocodeEventLocations: failed', [
                        'event_id' => $meta->event_id,
                        'location' => $meta->location,
                        'error'    => $e->getMessage(),
                    ]);
                }
            }
        }, 'event_id');

        Log::info('GeocodeEventLocations: done', compact('processed', 'created', 'updated', 'skipped', 'failed'));
    }
}