-- Addendum to supabase/schema.sql + functions.sql — run after those.
--
-- employee_history intentionally has no UPDATE/DELETE policy (§4.9's
-- "unveraenderbar" / append-only requirement). But undo_reorg needs to
-- remove the specific history rows a reorg created — found via live
-- testing: the DELETE inside undo_reorg silently matched 0 rows under RLS
-- (no error, since RLS just filters DELETE-eligible rows to none), leaving
-- Reorganisation entries behind after an otherwise-successful undo.
--
-- Scope the exception as narrowly as possible: hr_admin may delete a
-- history row only if it carries a reorg_scenario_id, i.e. only rows
-- apply_reorg created. Eintritt/Austritt/Beförderung/etc. rows (always
-- reorg_scenario_id IS NULL) remain fully immutable.
create policy "history_delete_admin_reorg_undo" on employee_history for delete
  using (is_hr_admin() and reorg_scenario_id is not null);