-- migrate:up

-- Cache layer for reverse geocoding. Keyed by lat/lng rounded to 4 decimals
-- (~10m grid), so parked vehicles share a row and only moving vehicles trigger
-- fresh lookups. Filled asynchronously by the geocoder cron job (PRD F2.3).
CREATE TABLE state.geocoded_positions (
    lat_rounded   numeric(8,4) NOT NULL,
    lng_rounded   numeric(8,4) NOT NULL,
    address       text,
    address_short text,
    source        text NOT NULL DEFAULT 'nominatim',
    fetched_at    timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (lat_rounded, lng_rounded)
);

-- migrate:down

DROP TABLE IF EXISTS state.geocoded_positions;