-- Allow members to cancel their own reservations and edit reason/comment.

-- 1. Expand status check constraint to include 'cancelled'
alter table public.reservations
  drop constraint reservations_status_check;

alter table public.reservations
  add constraint reservations_status_check
  check (status in ('pending', 'tentative', 'confirmed', 'cancelled'));

-- 2. Exclude cancelled reservations from the public slots view so cancelled
--    slots appear as available again on the schedule.
drop view if exists public.reservation_slots;

create view public.reservation_slots
  with (security_invoker = false)
as
  select id, boat_id, start_time, end_time, status
  from public.reservations
  where status <> 'cancelled';

grant select on public.reservation_slots to authenticated;