"""
Assign initial system-wide resources-access privileges to sys-admins.
"""
import contextlib

from yoyo import step

def system_administrator_role_id(cursor):
    """Fetch ID for role 'system-administrator'."""
    cursor.execute(
        "SELECT role_id FROM roles WHERE role_name='system-administrator'")
    return cursor.fetchone()[0]


def assign_system_wide_resource_access_to_sysadmin(conn):
    """
    Assign initial system-wide resources-access privileges to
    `system-administrator` role.
    """
    with contextlib.closing(conn.cursor()) as cursor:
        sysadmin_role_id = system_administrator_role_id(cursor)
        cursor.executemany(
            "INSERT INTO role_privileges(role_id, privilege_id) "
            "VALUES(?, ?)",
            ((sysadmin_role_id, "system:resource:view"),
             (sysadmin_role_id, "system:resource:edit"),
             (sysadmin_role_id, "system:resource:delete"),
             (sysadmin_role_id, "system:resource:reassign-group"),
             (sysadmin_role_id, "system:resource:assign-owner")))


def revoke_system_wide_resource_access_from_sysadmin(conn):
    """
    Revoke initial system-wide resources-access privileges from
    `system-administrator` role.
    """
    with contextlib.closing(conn.cursor()) as cursor:
        sysadmin_role_id = system_administrator_role_id(cursor)
        cursor.executemany(
            "DELETE FROM role_privileges "
            "WHERE role_id=? AND privilege_id=?",
            ((sysadmin_role_id, "system:resource:view"),
             (sysadmin_role_id, "system:resource:edit"),
             (sysadmin_role_id, "system:resource:delete"),
             (sysadmin_role_id, "system:resource:reassign-group"),
             (sysadmin_role_id, "system:resource:assign-owner")))

__depends__ = {'20250729_01_CNn2p-create-initial-system-wide-resources-access-privileges'}

steps = [
    step(assign_system_wide_resource_access_to_sysadmin,
         revoke_system_wide_resource_access_from_sysadmin)
]