--1) Geometry types: box_ops, circle_ops, point_ops, poly_ops
--These built-in GiST operator classes support geometric predicates such as overlap, containment, and nearest-neighbor, depending on the type/operator.

--1.1 point_ops — courier / taxi / ATM nearest-location search
DROP TABLE IF EXISTS delivery_points;
CREATE TABLE delivery_points
(
    point_id     bigserial PRIMARY KEY,
    city         text NOT NULL,
    driver_name  text NOT NULL,
    status       text NOT NULL,
    loc          point NOT NULL
);

INSERT INTO delivery_points (city, driver_name, status, loc)
SELECT
    CASE (g % 3)
        WHEN 0 THEN 'Yerevan'
        WHEN 1 THEN 'Gyumri'
        ELSE 'Vanadzor'
    END,
    'driver_' || g,
    CASE WHEN g % 10 = 0 THEN 'busy' ELSE 'free' END,
    point(
        round((40 + random() * 10)::numeric, 4),
        round((44 + random() * 10)::numeric, 4)
    )
FROM generate_series(1, 200000) g;

CREATE INDEX ix_delivery_points_gist
    ON delivery_points
 USING gist (loc);

select * from delivery_points;
--Nearest available driver to customer
SELECT point_id,
       driver_name,
       loc,
       loc <-> point(44.5123, 40.1772) AS distance
FROM delivery_points
WHERE city = 'Yerevan'
  AND status = 'free'
ORDER BY loc <-> point(44.5123, 40.1772)
LIMIT 10;
--Drivers inside rectangular area
SELECT *
FROM delivery_points
WHERE loc <@ box(point(44.45, 40.10), point(44.60, 40.25));
-------------------------------
-------------------------------
--1.2 box_ops — warehouse occupancy / map windows
DROP TABLE IF EXISTS warehouse_zones;
CREATE TABLE warehouse_zones
(
    zone_id      bigserial PRIMARY KEY,
    warehouse    text NOT NULL,
    zone_name    text NOT NULL,
    area         box  NOT NULL
);

INSERT INTO warehouse_zones (warehouse, zone_name, area)
SELECT
    'WH_' || ((g % 20) + 1),
    'ZONE_' || g,
    box(
        point(g % 100, g % 70),
        point((g % 100) + 5 + (g % 7), (g % 70) + 4 + (g % 5))
    )
FROM generate_series(1, 50000) g;

CREATE INDEX ix_warehouse_zones_gist
    ON warehouse_zones
 USING gist (area);
--Find zones overlapping a planned robot route rectangle
SELECT zone_id, warehouse, zone_name
FROM warehouse_zones
WHERE area && box(point(20, 20), point(35, 35));
--Find zones fully containing a smaller target area
SELECT zone_id, warehouse, zone_name
FROM warehouse_zones
WHERE area @> box(point(24, 24), point(25, 25));