# query to retrieve all the roles and ids
role_ids = 'SELECT orchadmin_role, orchadmin_role_id FROM orchadmin_roles;'

# query to retrieve user information for specific role name
specific_role_query = '''
  SELECT
      ou.login AS `Login`,
      ou.`email` AS `Email`,
      ou.f_name AS `First Name`,
      ou.l_name AS `Last Name`,
      NOW() AS `Today Date`,
      (SELECT login_time FROM login_stat
          WHERE user_id = ou.id AND user_type = 'orchadmin' ORDER BY id desc LIMIT 1) AS `Last Login`
  FROM
      orchadmin_users AS ou
      INNER JOIN orchadmin_user_roles AS our ON our.orchadmin_user_id = ou.id
      INNER JOIN orchadmin_roles AS orr ON orr.orchadmin_role_id = our.orchadmin_role_id
  WHERE
      ou.active = 'Y'
      AND orr.`orchadmin_role` = %s
  ORDER BY
      ou.f_name,
      ou.l_name;
'''


def all_roles_query(role_dict):
    """
    Creates a SQL query based on provided roles and ids in a dictionary format.

    Args:
      role_dict (dict): A dictionary of roles where keys are role names.

    Returns:
      str: A SQL query as a string.
    """

    # Create a parameterized query for the role names (IN clause)
    role_names_query = ', '.join(['%s'] * len(role_dict))

    role_case_statements = ', '.join(
        [
            f"MAX(CASE WHEN our.orchadmin_role_id = %s THEN 'YES' ELSE 'NO' END) AS `{role_name}`"
            for role_name in role_dict.keys()
        ]
    )

    # Create the main query with placeholders for role_ids and role_names
    user_query = f'''
    SELECT
        ou.id AS user_id,
        ou.login,
        ou.email,
        ou.f_name AS first_name,
        ou.l_name AS last_name,
        NOW() AS today_date,
        ou.title,
        (SELECT login_time FROM login_stat
            WHERE user_id = ou.id AND user_type = 'orchadmin' ORDER BY id desc LIMIT 1) AS `Last Login`,
        ou.department,
        {role_case_statements}
    FROM orchadmin_users AS ou
    INNER JOIN orchadmin_user_roles AS our ON our.orchadmin_user_id = ou.id
    INNER JOIN orchadmin_roles AS orr ON orr.orchadmin_role_id = our.orchadmin_role_id
    WHERE
        ou.active = 'Y'
        AND orr.orchadmin_role IN ({role_names_query})
    GROUP BY
        ou.id, ou.login, ou.email, ou.f_name, ou.l_name, ou.title, ou.department
    ORDER BY
        ou.f_name, ou.l_name;
    '''

    return user_query