-- Pincode → coordinates, district and state.
--
-- The keystone of doc 25 §10.3. One static table that serves four jobs:
--
--   1. Distance pricing — the centroid to measure a delivery to
--   2. Metro detection  — better than guessing from a city name
--   3. ODA detection    — the zone that quietly loses money
--   4. Checkout         — autofill city and state from a pincode
--
-- Seeded from `scripts/seed-pincode-geo.mjs`, which reads a CSV. India has
-- ~19,300 pincodes and that data is public (data.gov.in / India Post), but it
-- is not something to invent: a wrong centroid produces a confident wrong
-- price. The seed script therefore ships with a curated set of ~90 well-known
-- district centroids and imports the rest from a CSV when one is supplied.
--
-- Nothing depends on this table being complete. An unknown pincode resolves to
-- no coordinates, which makes the order Outstation — priced by zone rather than
-- by distance, which is correct rather than merely safe.
--
-- Idempotent; safe to re-run.
BEGIN;

CREATE TABLE IF NOT EXISTS store.pincode_geo (
  pincode   TEXT PRIMARY KEY,
  latitude  DECIMAL(9,6) NOT NULL,
  longitude DECIMAL(9,6) NOT NULL,
  district  TEXT NOT NULL,
  state     TEXT NOT NULL,
  "isMetro" BOOLEAN NOT NULL DEFAULT FALSE,
  "isOda"   BOOLEAN NOT NULL DEFAULT FALSE,
  -- TRUE when this row is a district centroid standing in for a whole area
  -- rather than a surveyed pincode centroid. Kept so a later full import can
  -- tell what it is allowed to overwrite.
  "isApproximate" BOOLEAN NOT NULL DEFAULT FALSE
);

ALTER TABLE store.pincode_geo DROP CONSTRAINT IF EXISTS pincode_geo_pincode_ck;
ALTER TABLE store.pincode_geo ADD CONSTRAINT pincode_geo_pincode_ck
  CHECK (pincode ~ '^[1-9][0-9]{5}$');

-- Inside India's bounding box. A transposed pair lands outside it, which is
-- what makes this catch the mistake a -90..90 check would wave through.
ALTER TABLE store.pincode_geo DROP CONSTRAINT IF EXISTS pincode_geo_bounds_ck;
ALTER TABLE store.pincode_geo ADD CONSTRAINT pincode_geo_bounds_ck CHECK (
  latitude BETWEEN 6 AND 38 AND longitude BETWEEN 66 AND 98
);

CREATE INDEX IF NOT EXISTS pincode_geo_state ON store.pincode_geo (state);

COMMENT ON TABLE store.pincode_geo IS
  'Pincode centroids. Incomplete by design — an unknown pincode falls through to zone pricing. See docs/25-india-shipping-redesign.md §10.3.';

COMMIT;