create extension if not exists btree_gist;

alter table public.bookings
  add column if not exists occupies tstzrange;

create or replace function public.bookings_set_occupies()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
  dur        integer;
  buf_before integer := 0;
  buf_after  integer := 0;
begin
  select coalesce(new.duration_minutes, s.duration_minutes, 30),
         coalesce(s.buffer_before_minutes, 0),
         coalesce(s.buffer_after_minutes, 0)
    into dur, buf_before, buf_after
    from public.services s
   where s.id = new.service_id;

  -- Occupancy window = [start - prep buffer, start + duration + post buffer)
  new.occupies := tstzrange(
    (new.booking_date + new.booking_time)::timestamptz - make_interval(mins => buf_before),
    (new.booking_date + new.booking_time)::timestamptz + make_interval(mins => dur + buf_after),
    '[)'
  );
  return new;
end $$;

drop trigger if exists bookings_set_occupies_trg on public.bookings;
create trigger bookings_set_occupies_trg
  before insert or update of booking_date, booking_time, duration_minutes, service_id
  on public.bookings
  for each row execute function public.bookings_set_occupies();

alter table public.bookings drop constraint if exists bookings_no_bay_overlap;
alter table public.bookings add constraint bookings_no_bay_overlap
  exclude using gist (bay_id with =, occupies with &&)
  where (status in ('pending', 'confirmed') and bay_id is not null);