-- Split review and delivery without changing identities, memberships or owners.
CREATE TABLE IF NOT EXISTS workbench_content_permission_migrations (
 version integer PRIMARY KEY,
 changes jsonb NOT NULL,
 created_at timestamptz NOT NULL DEFAULT now()
);

DO $migration$
DECLARE r record; saved jsonb; before_permissions jsonb; next_permissions jsonb;
 mapped_permissions jsonb; source_permissions jsonb; changes jsonb := '[]'::jsonb;
 restored boolean;
BEGIN
 PERFORM pg_advisory_xact_lock(207, 11905);
 IF EXISTS(SELECT 1 FROM workbench_content_permission_migrations WHERE version=207) THEN RETURN; END IF;
 LOCK TABLE workbench_access_roles IN SHARE ROW EXCLUSIVE MODE;
 FOR r IN SELECT * FROM workbench_access_roles ORDER BY tenant_id,id LOOP
  saved := NULL;
  IF to_regclass('yawan_legacy_access_mappings') IS NOT NULL THEN
   EXECUTE 'SELECT source_spec FROM yawan_legacy_access_mappings
     WHERE source_system=$1 AND target_tenant_id=$2 AND entity_key LIKE ''role/%''
       AND source_spec->>''id''=$3'
     INTO saved USING 'yawan-workbench',r.tenant_id,r.id;
  END IF;
  before_permissions := r.permissions;
  -- Before this split, readers could export and producers could approve/review.
  SELECT coalesce(jsonb_agg(p ORDER BY p),'[]'::jsonb) INTO next_permissions FROM (
   SELECT DISTINCT p FROM (
    SELECT jsonb_array_elements_text(r.permissions) AS p
    UNION ALL SELECT 'content.review' WHERE r.permissions ? 'content.write'
    UNION ALL SELECT 'content.deliver' WHERE r.permissions ? 'content.read'
   ) existing
  ) permissions;
  mapped_permissions := NULL;
  restored := false;
  IF saved IS NOT NULL THEN
   source_permissions := saved->'source_permissions';
   SELECT coalesce(jsonb_agg(p ORDER BY p),'[]'::jsonb) INTO mapped_permissions
    FROM jsonb_array_elements_text(saved->'permissions') p WHERE p NOT LIKE 'content.%';
   -- Legacy task GETs required tasks:review; generic read alone granted no task access.
   IF source_permissions ? 'tasks:review' OR source_permissions ? 'review' THEN
    mapped_permissions := mapped_permissions || '["content.read","content.review"]'::jsonb;
    IF source_permissions ? 'tasks:write' OR source_permissions ? 'content:write' THEN
     mapped_permissions := mapped_permissions || '"content.write"'::jsonb;
    END IF;
    IF source_permissions ? 'tasks:deliver' OR source_permissions ? 'content:write' THEN
     mapped_permissions := mapped_permissions || '"content.deliver"'::jsonb;
    END IF;
   END IF;
   SELECT coalesce(jsonb_agg(p ORDER BY p),'[]'::jsonb) INTO mapped_permissions
    FROM jsonb_array_elements_text(mapped_permissions) p;
   -- An edited imported role keeps its current native policy.
   IF r.version=1 AND r.name=saved->>'name' AND r.permissions=saved->'permissions' THEN
    next_permissions := mapped_permissions;
    restored := true;
   END IF;
  END IF;
  IF next_permissions IS DISTINCT FROM before_permissions THEN
   UPDATE workbench_access_roles SET permissions=next_permissions,version=version+1,updated_at=now()
    WHERE id=r.id AND tenant_id=r.tenant_id;
  END IF;
  changes := changes || jsonb_build_array(jsonb_build_object(
   'role_id',r.id,'tenant_id',r.tenant_id,'before',before_permissions,'after',next_permissions,
   'legacy_source_spec',saved,'mapped_permissions',mapped_permissions,'legacy_restored',restored));
 END LOOP;
 INSERT INTO workbench_content_permission_migrations(version,changes) VALUES(207,changes);
END $migration$;