-- +goose Up
-- Adds the manager role between recruiter and admin.
--
-- Today the hierarchy jumps straight from recruiter to admin, and admin carries privacy.erase,
-- audit.read, compliance.write and user.invite. A recruiting team lead needs none of those. What they
-- need is to see the whole team's book and move work between people, and the only way to grant that
-- right now is to make them an admin, which also hands them PII deletion and account management.
--
-- That is how over permissioning happens in practice: not by a bad decision, but because the smallest
-- role that can do the job is far larger than the job.
--
-- The rank order becomes viewer 0, member 1, recruiter 2, manager 3, admin 4, owner 5. Ranks are
-- compared, never stored, so inserting a tier rewrites no rows; the only persisted thing is the role
-- name, and this constraint is what admits it.
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

-- The invite table carries its OWN copy of the vocabulary, and widening only the users table left
-- inviting a manager failing with a 500. Found by trying it against the live API rather than by reading
-- the schema: the role reached the invite row before it ever reached a user row.
--
-- Two tables holding the same enum is the underlying problem, and the pair is now changed together and
-- rolled back together so they cannot drift again.
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));

-- +goose Down
-- Existing managers must land somewhere the old constraint permits. Recruiter is the honest choice: it
-- is the tier they were promoted FROM, so a rollback removes the added authority rather than granting
-- more of it, which is the safe direction for a permission change to fail in.
UPDATE users SET role = 'recruiter' WHERE role = 'manager';
DELETE FROM user_invites WHERE role = 'manager';
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
    CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer'));
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
    CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer'));