Geospatial store locator

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TYPE vehicle_status AS ENUM ('idle', 'en_route', 'maintenance');

CREATE TABLE regions (
    id serial PRIMARY KEY,
    name text NOT NULL UNIQUE,
    boundary geometry(MultiPolygon,4326) NOT NULL
);
CREATE INDEX regions_boundary_gix ON regions USING gist (boundary);

CREATE TABLE stores (
    id bigserial PRIMARY KEY,
    region_id integer,
    name text NOT NULL,
    address text,
    location geometry(Point,4326) NOT NULL,
    opened_on date
);
CREATE INDEX stores_location_gix ON stores USING gist (location);

CREATE TABLE delivery_zones (
    id bigserial PRIMARY KEY,
    store_id bigint NOT NULL,
    name text NOT NULL,
    area geography(Polygon,4326) NOT NULL,
    fee_cents integer DEFAULT 0 NOT NULL,
    CHECK (fee_cents >= 0)
);
CREATE INDEX delivery_zones_area_gix ON delivery_zones USING gist (area);

CREATE TABLE customers (
    id bigserial PRIMARY KEY,
    name text NOT NULL,
    home geometry(Point,4326)
);
CREATE INDEX customers_home_gix ON customers USING gist (home);

CREATE TABLE vehicles (
    id serial PRIMARY KEY,
    store_id bigint NOT NULL,
    plate text NOT NULL UNIQUE,
    status vehicle_status DEFAULT 'idle' NOT NULL,
    last_position geometry(PointZ,4326)
);

CREATE TABLE deliveries (
    id bigserial PRIMARY KEY,
    vehicle_id integer NOT NULL,
    customer_id bigint NOT NULL,
    route geometry(LineString,4326),
    delivered_at timestamptz
);
CREATE INDEX deliveries_route_gix ON deliveries USING gist (route);

ALTER TABLE stores ADD FOREIGN KEY (region_id) REFERENCES regions (id);

ALTER TABLE delivery_zones ADD FOREIGN KEY (store_id) REFERENCES stores (id) ON DELETE CASCADE;

ALTER TABLE vehicles ADD FOREIGN KEY (store_id) REFERENCES stores (id);

ALTER TABLE deliveries ADD FOREIGN KEY (vehicle_id) REFERENCES vehicles (id);

ALTER TABLE deliveries ADD FOREIGN KEY (customer_id) REFERENCES customers (id);