"""stop a membership row demoting staff on that project

0003 gave every pre-existing `project_members` row the default `reviewer`
level. A per-project grant beats the app-wide level, so an admin who happened
to be a member of a project was silently demoted to reviewer *on that project*
— they kept `all_instances` everywhere else, which made it look like a
one-project glitch rather than a migration fault.

This realigns any grant that sits below its holder's app-wide level, for
accounts whose level applies everywhere. Deliberately narrow: a grant that
intentionally scopes someone DOWN on one project is a real feature, so this
only touches accounts with `all_instances`, for whom a lower grant is never
something a person chose — it is the default the migration wrote.

Idempotent, and a no-op on a database that never carried pre-0003 data.

Revision ID: 0004_staff_grants_not_demoting
Revises: 0003_per_project_levels
Create Date: 2026-08-18
"""

from collections.abc import Sequence

from alembic import op

revision: str = "0004_staff_grants_not_demoting"
down_revision: str | None = "0003_per_project_levels"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.execute(
        """
        UPDATE project_members AS pm
           SET level = a.level
          FROM app_accounts AS a
         WHERE a.username = pm.username
           AND a.all_instances IS TRUE
           AND pm.level <> a.level
        """
    )


def downgrade() -> None:
    # There is no way to tell a corrected row from one that always held this
    # level, and reinstating the demotion would be reintroducing the fault.
    pass