"""permissions roles + project (job function) role

Adds `users.project_role` (job function on the project — carries no permissions)
and migrates the permissions vocabulary: the legacy role 'user' becomes
'project_user'. 'admin' is untouched; 'project_admin' is new and is only ever
granted explicitly from the admin console.

Revision ID: b41c7ae90d52
Revises: 57dec34f11cb
Create Date: 2026-08-03 15:12:04.118322
"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = 'b41c7ae90d52'
down_revision = '57dec34f11cb'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # server_default backfills existing rows (the column is NOT NULL).
    op.add_column('users', sa.Column('project_role', sa.String(length=120),
                                     nullable=False, server_default=''))
    # Legacy 'user' means exactly what 'project_user' means now.
    op.execute("UPDATE users SET role = 'project_user' WHERE role = 'user'")


def downgrade() -> None:
    # Fold the new role back onto the legacy value so an older build still reads
    # the table. A project_admin loses its elevated rights on downgrade.
    op.execute("UPDATE users SET role = 'user' WHERE role IN ('project_user', 'project_admin')")
    op.drop_column('users', 'project_role')