"""
Add sysadmin privileges for acting on groups: mostly handling user management.
"""
import itertools
import contextlib

from yoyo import step

__depends__ = {'20250729_03_oCvvq-grant-role-to-all-resources-to-sys-admin-users'}


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 add_group_privileges_to_sysadmin_role(conn):
    """Add group-management privileges to sysadmin role."""
    with contextlib.closing(conn.cursor()) as cursor:
        sysadminroleid = system_administrator_role_id(cursor)
        cursor.executemany(
            "INSERT INTO role_privileges(role_id, privilege_id) VALUES (?, ?)",
            tuple(itertools.product(
                (sysadminroleid,),
                ('system:group:add-group-member',
                 'system:group:remove-group-member',
                 'system:group:assign-group-leader',
                 'system:group:revoke-group-leader'))))


def remove_group_privileges_to_sysadmin_role(conn):
    """Remove group-management privileges from sysadmin role."""
    with contextlib.closing(conn.cursor()) as cursor:
        sysadminroleid = system_administrator_role_id(cursor)
        cursor.executemany(
            "DELETE FROM role_privileges WHERE role_id=? AND privilege_id=?",
            tuple(itertools.product(
                (sysadminroleid,),
                ('system:group:add-group-member',
                 'system:group:remove-group-member',
                 'system:group:assign-group-leader',
                 'system:group:revoke-group-leader'))))


steps = [
    step(
        """
        INSERT INTO privileges(privilege_id, privilege_description)
        VALUES
          ('system:group:add-group-member',
           'Make an existing user a member of a group.'),
          ('system:group:remove-group-member',
           'Remove a member user from a group.'),
          ('system:group:assign-group-leader',
           'Assign an existing group member the group-leader role'),
          ('system:group:revoke-group-leader',
           'Revoke the group-leader role from a group member with the role.')
        """,
        """
        DELETE FROM privileges WHERE privilege_id IN
        ('system:group:add-group-member',
         'system:group:remove-group-member',
         'system:group:assign-group-leader',
         'system:group:revoke-group-leader')
        """),
    step(add_group_privileges_to_sysadmin_role,
         remove_group_privileges_to_sysadmin_role)
]