-- Updates the status + review (signoff) vocabularies and migrates existing data.
-- Idempotent: re-running maps only known OLD values, leaving current ones intact.

alter table pages drop constraint if exists pages_status_check;
alter table pages drop constraint if exists pages_signoff_check;

update pages set status =
  case status
    when 'not done' then 'open'
    when 'in progress' then 'creating'
    when 'content ready' then 'drafted'
    else status
  end;

update pages set signoff =
  case signoff
    when 'Not Reviewed' then 'not ready'
    when 'No Review Needed' then 'not ready'
    when 'Revisions Needed' then 'revision needed'
    when 'OK' then 'approved'
    else signoff
  end;

alter table pages alter column status set default 'open';
alter table pages alter column signoff set default 'not ready';

alter table pages add constraint pages_status_check
  check (status in ('open', 'creating', 'finalizing', 'revision pending', 'revising', 'drafted', 'completed'));

alter table pages add constraint pages_signoff_check
  check (signoff in ('not ready', 'ready for review', 'revision needed', 'approved'));
