-- Lets an agent ask to cancel their own already-approved time off,
-- without letting them silently undo it themselves. Confirmed with the
-- user: self-cancel of an approved request needs admin approval first
-- (same review step as a brand-new request), NOT an instant self-revoke
-- -- staffing may already be planned around that absence.
--
-- Deliberately a separate boolean flag on the existing row, not a new
-- `status` value: `status` already has a check constraint locked to
-- ('pending','approved','denied','revoked') (migration 0040), and the
-- row needs to stay `status='approved'` (still a real day off, still
-- billing correctly via invoiceLogic.js) right up until an admin acts
-- on the cancellation -- adding a 5th status would mean the invoice/
-- schedule-sync logic would also need to treat that 5th value as
-- equivalent to 'approved' everywhere, whereas a flag on top of the
-- existing status needs no such change anywhere else.
--
-- No new RLS policy needed for agents to set this flag -- the existing
-- time_off_requests_update policy is approver-only (0040), and stays
-- that way. The new /api/time-off/request-cancel route (service-role,
-- same pattern as /api/time-off/decide) is the only way to set it,
-- checking the caller owns the request themselves.

alter table public.time_off_requests
  add column if not exists cancel_requested boolean not null default false,
  add column if not exists cancel_requested_at timestamptz;

-- ============================================================
-- Self-verification guard
-- ============================================================

do $migration_guard$
declare
  col_count int;
begin
  select count(*) into col_count from information_schema.columns
    where table_schema = 'public' and table_name = 'time_off_requests' and column_name in ('cancel_requested', 'cancel_requested_at');
  if col_count <> 2 then
    raise exception 'Aborting 0042: expected 2 new columns on time_off_requests, found %', col_count;
  end if;
end $migration_guard$;