-- Customer coordinates on the delivery address.
--
-- Nullable, and expected to stay null for most orders: the pincode centroid is
-- the always-available answer (doc 25 §10.2) and these columns are the
-- refinement a map pin or a granted geolocation supplies. Adding them now means
-- distance-based pricing needs no schema change the day that lands.
--
-- They are a HINT, never a price input on their own — see §10.6. The server
-- prices from the saved address and ignores coordinates that disagree with the
-- address's own pincode.
--
-- Idempotent; safe to re-run.
BEGIN;

ALTER TABLE admin.addresses
  ADD COLUMN IF NOT EXISTS latitude  DECIMAL(9,6),
  ADD COLUMN IF NOT EXISTS longitude DECIMAL(9,6);

ALTER TABLE admin.addresses DROP CONSTRAINT IF EXISTS addresses_coords_ck;
ALTER TABLE admin.addresses ADD CONSTRAINT addresses_coords_ck CHECK (
  (latitude IS NULL AND longitude IS NULL)
  OR (latitude BETWEEN -90 AND 90 AND longitude BETWEEN -180 AND 180)
);

COMMENT ON COLUMN admin.addresses.latitude IS
  'Optional refinement from a map pin or geolocation. Validated against the pincode before it may affect pricing.';

COMMIT;