-- Normalize existing roles to the Web authorization model.
-- This migration is intentionally re-runnable.

ALTER TABLE users
    DROP CONSTRAINT IF EXISTS users_role_check;

UPDATE users
SET role = 'user'
WHERE role NOT IN ('admin', 'user');

ALTER TABLE users
    ADD CONSTRAINT users_role_check
    CHECK (role IN ('admin', 'user'));

ALTER TABLE user_project_membership
    DROP CONSTRAINT IF EXISTS user_project_membership_role_check;

UPDATE user_project_membership
SET project_role = CASE
    WHEN project_role IN (
        'owner',
        'admin',
        'modeler',
        'dispatcher'
    ) THEN 'member'
    ELSE 'viewer'
END
WHERE project_role NOT IN ('member', 'viewer');

ALTER TABLE user_project_membership
    ADD CONSTRAINT user_project_membership_role_check
    CHECK (project_role IN ('member', 'viewer'));