-- Create extension for gist index if not exists
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Add exclusion constraint to prevent overlapping bookings for the same place
-- Only applies to non-cancelled bookings
-- tstzrange because columns are timestamp with time zone
ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings 
EXCLUDE USING gist (place_id WITH =, tstzrange(start_time, end_time) WITH &&)
WHERE (status != 'cancelled');