-- +goose Up
-- The OPERATOR role, amendment 6 decision 1. A new top level above and outside the tenant ladder: the
-- platform plane. Operator holds every platform capability and every tenant capability in every tenant,
-- every operator action is audited, and MFA is mandatory for it from the day the role exists.
--
-- This EXTENDS the vocabulary. No existing value is renamed, reordered, or removed, per amendment 6 section
-- 0 item 4, so every row already in users and user_invites stays valid and no historical role is rewritten.
-- Superuser keeps its value and its rows; what changes is its MEANING, from "the platform seat and the
-- client seat at once" to "the client's top seat within its tenant", and that meaning lives in code and in
-- which surfaces answer 404, not in the enum.
--
-- Both tables move together. user_invites carries the same vocabulary because an invitation names the role
-- it will confer, and a constraint that refused operator there would make the operator seat uninvitable
-- while the users table happily stored it.

ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('operator', 'superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('operator', 'superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

-- +goose Down
-- Refuse to narrow the vocabulary while operator rows exist. Dropping to the old CHECK would fail on those
-- rows anyway; failing with a count says WHY rather than surfacing a bare constraint violation, and it
-- protects the platform seat from being silently demoted by a rollback.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT (SELECT count(*) FROM users WHERE role = 'operator')
       + (SELECT count(*) FROM user_invites WHERE role = 'operator') INTO n;
  IF n > 0 THEN
    RAISE EXCEPTION 'refusing to remove the operator role: % operator user or invite rows present', n;
  END IF;
END $$;
-- +goose StatementEnd
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));