--Network type: inet_ops
--GiST supports inet_ops for inet/cidr, useful for subnet containment and overlap style logic. PostgreSQL notes inet_ops is not the default, so specify it explicitly.

--IP fraud / access-control / routing example
DROP TABLE IF EXISTS ip_allocations;
CREATE TABLE ip_allocations
(
    alloc_id      bigserial PRIMARY KEY,
    owner_name    text NOT NULL,
    network_range inet NOT NULL,
    risk_level    int  NOT NULL
);

INSERT INTO ip_allocations (owner_name, network_range, risk_level)
SELECT
    'org_' || g,
    ('10.' || (g % 255) || '.' || ((g / 255) % 255) || '.0/24')::inet,
    1 + (g % 5)
FROM generate_series(1, 120000) g;

CREATE INDEX ix_ip_allocations_gist
    ON ip_allocations
 USING gist (network_range inet_ops);
--Which network owns this suspicious IP
SELECT alloc_id, owner_name, network_range, risk_level
FROM ip_allocations
WHERE network_range >>= '10.12.44.17'::inet;
--All allocations overlapping a broader block
SELECT alloc_id, owner_name, network_range
FROM ip_allocations
WHERE network_range && '10.12.44.0/22'::inet;
--------------------------------------
--------------------------------------
--Range types: range_ops
--This is one of the most important GiST families. PostgreSQL range types such as int4range, numrange, daterange, tsrange, and tstzrange work with GiST for overlap, containment, adjacency, left/right relations, and exclusion constraints.

--3.1 tsrange — booking system
DROP TABLE IF EXISTS room_bookings;
CREATE TABLE room_bookings
(
    booking_id    bigserial PRIMARY KEY,
    room_id       int NOT NULL,
    customer_id   int NOT NULL,
    booking_slot  tsrange NOT NULL,
    amount        numeric(12,2) NOT NULL
);

INSERT INTO room_bookings (room_id, customer_id, booking_slot, amount)
SELECT
    1 + (g % 500),
    100000 + g,
    tsrange(
        timestamp '2026-01-01 08:00:00'
            + ((g % 90) * interval '1 day')
            + ((g % 12) * interval '1 hour'),
        timestamp '2026-01-01 09:00:00'
            + ((g % 90) * interval '1 day')
            + ((g % 12) * interval '1 hour')
            + ((1 + (g % 4)) * interval '30 min'),
        '[)'
    ),
    round((50 + random() * 200)::numeric, 2)
FROM generate_series(1, 300000) g;

CREATE INDEX ix_room_bookings_gist
    ON room_bookings
 USING gist (booking_slot);
--Find overlapping bookings for a requested slot
SELECT booking_id, room_id, booking_slot
FROM room_bookings
WHERE room_id = 101
  AND booking_slot && tsrange('2026-03-10 11:00', '2026-03-10 13:00', '[)');
--Find bookings containing a timestamp
SELECT booking_id, room_id, booking_slot
FROM room_bookings
WHERE booking_slot @> timestamp '2026-03-10 11:30:00';