--Multrange
DROP TABLE IF EXISTS doctor_availability;
CREATE TABLE doctor_availability
(
    doctor_id       int PRIMARY KEY,
    doctor_name     text NOT NULL,
    available_slots tsmultirange NOT NULL
);

INSERT INTO doctor_availability
VALUES
(
    1,
    'Dr. Aram',
    tsmultirange(
        tsrange('2026-04-05 09:00', '2026-04-05 12:00', '[)'),
        tsrange('2026-04-05 14:00', '2026-04-05 17:00', '[)'),
        tsrange('2026-04-06 10:00', '2026-04-06 13:00', '[)')
    )
),
(
    2,
    'Dr. Mariam',
    tsmultirange(
        tsrange('2026-04-05 08:00', '2026-04-05 11:30', '[)'),
        tsrange('2026-04-05 15:00', '2026-04-05 19:00', '[)')
    )
);

CREATE INDEX ix_doctor_availability_gist
    ON doctor_availability
 USING gist (available_slots);
--Doctors available at exact timestamp
SELECT doctor_id, doctor_name
FROM doctor_availability
WHERE available_slots @> timestamp '2026-04-05 10:15';
--Doctors whose availability overlaps requested slot
SELECT doctor_id, doctor_name
FROM doctor_availability
WHERE available_slots && tsrange('2026-04-05 16:00', '2026-04-05 16:45', '[)');