LinkDesk/backend/migrate_role_permissions.py

#!/usr/bin/env python3
"""
One-off migration script for the multi-role permission system.

Creates the new roles/permissions/role_permissions/user_roles tables, seeds
the permission catalog (asset/shot/task create/edit/delete, plus assignment,
review, submission, upload, and note actions), seeds the 4 system roles
(coordinator/director/artist/developer) with role_permissions matching
today's actual behavior, then backfills user_roles from each existing
user's legacy `role` column.

Does not read or write users.role or users.is_admin beyond reading `role` to
resolve which system Role to link. Safe to re-run (every step is idempotent
and purely additive - re-running after adding new PERMISSIONS/grants below
only inserts what's missing, never removes or resets existing data).
"""

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from database import DATABASE_URL, Base
from models.user import User
from models.role import Role, Permission
import logging

import models  # noqa: F401 - ensures every model is registered on Base.metadata

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# The full permission catalog: (resource, action, description).
PERMISSIONS = [
    ("asset", "create", "Can create assets"),
    ("asset", "edit", "Can edit assets"),
    ("asset", "delete", "Can delete assets"),
    ("shot", "create", "Can create shots"),
    ("shot", "edit", "Can edit shots"),
    ("shot", "delete", "Can delete shots"),
    ("task", "create", "Can create tasks"),
    ("task", "edit", "Can edit tasks"),
    ("task", "delete", "Can delete tasks"),
    ("task", "change_status", "Can change task status"),
    ("assignment", "create", "Can assign a task to a user"),
    ("assignment", "edit", "Can reassign a task to a different user"),
    ("assignment", "delete", "Can unassign a task"),
    ("review", "publish", "Can approve a submission"),
    ("review", "retake", "Can request a retake on a submission"),
    ("submission", "create", "Can submit work for a task"),
    ("submission", "edit_self", "Can edit your own submission notes"),
    ("submission", "delete_self", "Can delete your own submission"),
    ("submission", "edit_other", "Can edit another user's submission notes"),
    ("submission", "delete_other", "Can delete another user's submission"),
    ("upload", "create", "Can upload task attachments"),
    ("upload", "delete", "Can delete task attachments"),
    ("note", "create", "Can add task notes"),
    ("note", "edit_self", "Can edit your own note"),
    ("note", "delete_self", "Can delete your own note"),
    ("note", "edit_other", "Can edit another user's note"),
    ("note", "delete_other", "Can delete another user's note"),
    ("note", "view_internal", "Can view internal notes"),
    ("note", "view_client", "Can view client notes"),
]

SYSTEM_ROLE_DESCRIPTIONS = {
    "coordinator": "Can create, edit, and delete assets, shots, and tasks.",
    "director": "Director role, migrated from the legacy single-role system.",
    "artist": "Artist role, migrated from the legacy single-role system.",
    "developer": "Developer role, migrated from the legacy single-role system.",
}

# Explicit per-role grants, faithful to today's actual behavior for each
# action (not a blanket "coordinator gets everything" - e.g. director
# already has real elevated rights on review publish/retake today, and
# non-artist roles already have unrestricted note/upload create today).
# submission:create is granted to nobody by default since today it's a pure
# ownership check (must literally be the assigned user), not role-based -
# is_admin still bypasses it, and self-service is preserved in the endpoint.
# note:view_internal goes to everyone (matches today - all notes are visible
# to anyone with task access); note:view_client is new/more restrictive by
# default, granted only to coordinator+director.
# task:change_status is granted to coordinator only. This deliberately
# resolves a pre-existing inconsistency: the single-task status endpoint only
# allowed COORDINATOR (+artist self-service), while the bulk endpoint also
# allowed DIRECTOR - now both use this one permission, so director loses the
# bulk-only status-change access they had before (can be re-granted via a
# custom role if that capability is actually wanted).
# note/submission edit_self+delete_self are granted to every system role -
# today, editing/deleting your OWN note or submission is an unconditional
# right with no role check at all, so faithful migration means everyone
# gets it by default. edit_other/delete_other (acting on someone ELSE's
# note or submission) are the elevated capability and are granted to
# nobody by default - not even coordinator.
SELF_SERVICE_GRANTS = {
    ("note", "edit_self"), ("note", "delete_self"),
    ("submission", "edit_self"), ("submission", "delete_self"),
}
SYSTEM_ROLE_GRANTS = {
    "coordinator": SELF_SERVICE_GRANTS | {
        ("asset", "create"), ("asset", "edit"), ("asset", "delete"),
        ("shot", "create"), ("shot", "edit"), ("shot", "delete"),
        ("task", "create"), ("task", "edit"), ("task", "delete"), ("task", "change_status"),
        ("assignment", "create"), ("assignment", "edit"), ("assignment", "delete"),
        ("review", "publish"), ("review", "retake"),
        ("upload", "create"), ("upload", "delete"),
        ("note", "create"), ("note", "view_internal"), ("note", "view_client"),
    },
    "director": SELF_SERVICE_GRANTS | {
        ("review", "publish"), ("review", "retake"),
        ("upload", "create"),
        ("note", "create"), ("note", "view_internal"), ("note", "view_client"),
    },
    "artist": SELF_SERVICE_GRANTS | {
        ("note", "view_internal"),
    },
    "developer": SELF_SERVICE_GRANTS | {
        ("upload", "create"),
        ("note", "create"), ("note", "view_internal"),
    },
}


def migrate_role_permissions():
    engine = create_engine(DATABASE_URL)
    SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

    Base.metadata.create_all(bind=engine)

    db = SessionLocal()

    try:
        # 0. Add note_type to production_notes if missing (create_all only
        # creates missing tables, it doesn't alter existing ones).
        try:
            db.execute(text("SELECT note_type FROM production_notes LIMIT 1"))
            logger.info("note_type column already exists")
        except Exception:
            db.rollback()
            logger.info("Adding note_type column to production_notes table")
            db.execute(text(
                "ALTER TABLE production_notes ADD COLUMN note_type VARCHAR(8) NOT NULL DEFAULT 'internal'"
            ))
            db.commit()

        # 0b. One-time rename: note/submission edit and delete were renamed
        # to edit_other/delete_other to make explicit that they only govern
        # acting on someone else's note or submission (your own is always
        # editable/deletable via ownership, no permission needed). Renaming
        # the existing rows in place (rather than leaving the old ones
        # orphaned and adding new ones) preserves their ids; idempotent -
        # a no-op once renamed. Not a general rename mechanism.
        RENAMES = [
            ("note", "edit", "edit_other", "Can edit another user's note"),
            ("note", "delete", "delete_other", "Can delete another user's note"),
            ("submission", "edit", "edit_other", "Can edit another user's submission notes"),
            ("submission", "delete", "delete_other", "Can delete another user's submission"),
        ]
        renamed = 0
        for resource, old_action, new_action, new_description in RENAMES:
            perm = db.query(Permission).filter(
                Permission.resource == resource, Permission.action == old_action
            ).first()
            if perm:
                perm.action = new_action
                perm.description = new_description
                renamed += 1
        db.commit()
        if renamed:
            logger.info(f"Permissions: {renamed} renamed to *_other")

        # 1. Seed permissions (idempotent by resource+action)
        permissions_by_key = {}
        created_permissions = 0
        for resource, action, description in PERMISSIONS:
            perm = db.query(Permission).filter(
                Permission.resource == resource, Permission.action == action
            ).first()
            if not perm:
                perm = Permission(resource=resource, action=action, description=description)
                db.add(perm)
                db.flush()
                created_permissions += 1
            permissions_by_key[(resource, action)] = perm
        logger.info(f"Permissions: {created_permissions} created, {len(permissions_by_key)} total")

        # 2. Seed system roles (idempotent by name)
        roles_by_name = {}
        created_roles = 0
        for name, description in SYSTEM_ROLE_DESCRIPTIONS.items():
            role = db.query(Role).filter(Role.name == name).first()
            if not role:
                role = Role(name=name, description=description, is_system=True)
                db.add(role)
                db.flush()
                created_roles += 1
            roles_by_name[name] = role
        logger.info(f"System roles: {created_roles} created, {len(roles_by_name)} total")

        # 3. Link role_permissions (idempotent - only add missing links)
        linked = 0
        for name, grants in SYSTEM_ROLE_GRANTS.items():
            role = roles_by_name[name]
            for key in grants:
                perm = permissions_by_key[key]
                if perm not in role.permissions:
                    role.permissions.append(perm)
                    linked += 1
        db.commit()
        logger.info(f"role_permissions: {linked} new links created")

        # 3b. One-time correction: an earlier version of this script granted
        # coordinator submission:edit/submission:delete by default, which was
        # wrong (those should be own-only by default, same as note:edit/
        # delete). Revoke them if still present, idempotent - a no-op once
        # corrected. Not a general revoke mechanism, just fixing this one
        # past mistake.
        coordinator = roles_by_name.get("coordinator")
        revoked = 0
        if coordinator:
            for key in [("submission", "edit"), ("submission", "delete")]:
                perm = permissions_by_key.get(key)
                if perm and perm in coordinator.permissions:
                    coordinator.permissions.remove(perm)
                    revoked += 1
        db.commit()
        if revoked:
            logger.info(f"role_permissions: {revoked} over-grant(s) revoked from coordinator (submission edit/delete correction)")

        # 4. Backfill user_roles from users.role (idempotent - only add missing links)
        users = db.query(User).all()
        backfilled = 0
        for user in users:
            role = roles_by_name.get(user.role.value)
            if role and role not in user.roles:
                user.roles.append(role)
                backfilled += 1
        db.commit()
        logger.info(f"user_roles: {backfilled} new links created for {len(users)} users")

        # Verification summary
        logger.info("--- Migration summary ---")
        for name, role in roles_by_name.items():
            logger.info(f"  Role '{name}': is_system={role.is_system}, permissions={len(role.permissions)}, users={len(role.users)}")

    except Exception as e:
        logger.error(f"Migration failed: {e}")
        db.rollback()
        raise
    finally:
        db.close()


if __name__ == "__main__":
    logger.info("Starting role/permission migration...")
    migrate_role_permissions()
    logger.info("Migration completed!")