docqube_backend/scripts/multi_role_preflight.py

"""
Read-only readiness report for retiring `users.role_id`.

**This script changes nothing.** It reports; the decisions are somebody's to make
with the numbers in front of them.

Two audiences:

1. **Before deploying the multi-role work.** Section A lists grants that stop
   conferring access once `PermissionService` filters on expiry and tenant, as
   `ScopeService` already does. Both were defects; the expectation is zero rows,
   and if it is not zero, those rows are the answer to "who is relying on a bug".

2. **Before retiring `users.role_id`** — the one irreversible step, deliberately
   left out of the multi-role release. Section B reports how far the data is from
   being able to lose the column: every user whose primary role has no equivalent
   tenant-wide `user_roles` row would lose that role the moment the column goes.

Run it against a copy of production first, because that is the environment whose
answers matter and none of this has ever met real data:

    APP_ENV=production python scripts/multi_role_preflight.py
"""

from __future__ import annotations

import os
import sys

sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import text  # noqa: E402

from app.db.database import engine  # noqa: E402


SECTION_A = {
    "expired grants that used to confer access through the coarse gate": """
        SELECT ur.id, ur.user_id, ur.group_id, r.name AS role_name,
               ur.expires_at, t.name AS tenant_name
        FROM user_roles ur
        JOIN roles r ON r.id = ur.role_id
        LEFT JOIN tenants t ON t.id = ur.tenant_id
        WHERE ur.expires_at IS NOT NULL AND ur.expires_at <= now()
        ORDER BY ur.expires_at DESC
    """,
    "grants naming another tenant's role": """
        SELECT ur.id, ur.user_id, ur.group_id, r.name AS role_name,
               ur.tenant_id AS grant_tenant, r.tenant_id AS role_tenant
        FROM user_roles ur
        JOIN roles r ON r.id = ur.role_id
        WHERE r.tenant_id IS NOT NULL
          AND ur.tenant_id IS NOT NULL
          AND r.tenant_id <> ur.tenant_id
    """,
    "grants naming neither a user nor a group": """
        SELECT ur.id, ur.role_id
        FROM user_roles ur
        WHERE ur.user_id IS NULL AND ur.group_id IS NULL
    """,
    "grants whose user no longer exists": """
        SELECT ur.id, ur.user_id
        FROM user_roles ur
        LEFT JOIN users u ON u.id = ur.user_id
        WHERE ur.user_id IS NOT NULL AND u.id IS NULL
    """,
}

SECTION_B = {
    "users whose primary role has no equivalent tenant-wide grant": """
        SELECT u.id, u.email, r.name AS primary_role, t.name AS tenant_name
        FROM users u
        JOIN roles r ON r.id = u.role_id
        LEFT JOIN tenants t ON t.id = u.tenant_id
        WHERE u.role_id IS NOT NULL
          AND u.is_deleted = false
          AND NOT EXISTS (
              SELECT 1 FROM user_roles ur
              WHERE ur.user_id = u.id
                AND ur.role_id = u.role_id
                AND ur.org_unit_id IS NULL
                AND (ur.expires_at IS NULL OR ur.expires_at > now())
          )
        ORDER BY t.name, u.email
    """,
    "users holding more than one role today": """
        SELECT u.id, u.email, count(ur.id) AS extra_grants
        FROM users u
        JOIN user_roles ur ON ur.user_id = u.id
        WHERE u.is_deleted = false
          AND (ur.expires_at IS NULL OR ur.expires_at > now())
          AND (u.role_id IS NULL OR ur.role_id <> u.role_id)
        GROUP BY u.id, u.email
        ORDER BY extra_grants DESC
    """,
    "users with no role at all, by either mechanism": """
        SELECT u.id, u.email, t.name AS tenant_name
        FROM users u
        LEFT JOIN tenants t ON t.id = u.tenant_id
        WHERE u.is_deleted = false
          AND u.role_id IS NULL
          AND NOT EXISTS (SELECT 1 FROM user_roles ur WHERE ur.user_id = u.id)
        ORDER BY t.name, u.email
    """,
}


def _run(title: str, queries: dict, expect_zero: bool) -> int:
    print(f"\n{'=' * 78}\n{title}\n{'=' * 78}")
    total = 0
    with engine.connect() as conn:
        for label, sql in queries.items():
            rows = conn.execute(text(sql)).mappings().all()
            total += len(rows)
            marker = "OK " if (not rows or not expect_zero) else "!! "
            print(f"\n{marker}{label}: {len(rows)}")
            for row in rows[:20]:
                print("     " + ", ".join(f"{k}={v!r}" for k, v in row.items()))
            if len(rows) > 20:
                print(f"     ... and {len(rows) - 20} more")
    return total


def main() -> int:
    print("Multi-role preflight — read-only. Nothing below writes to the database.")

    a = _run(
        "A. Grants that stop conferring access once the resolver is corrected\n"
        "   Expect zero. Any row here is somebody relying on a defect.",
        SECTION_A,
        expect_zero=True,
    )

    _run(
        "B. Distance from being able to drop users.role_id\n"
        "   Informational. The first list is the backfill that step would need.",
        SECTION_B,
        expect_zero=False,
    )

    print(f"\n{'=' * 78}")
    if a:
        print(
            f"!! {a} row(s) in section A. Read them before deploying: each one is an\n"
            "   access that exists today and will not exist afterwards."
        )
    else:
        print("OK Section A is clean — the resolver fix takes nothing away.")
    print(
        "   Section B is not a blocker for the multi-role release. `users.role_id`\n"
        "   stays as the primary role; retiring it is a separate, deliberate step."
    )
    return 0


if __name__ == "__main__":
    raise SystemExit(main())