DELIMITER $$

CREATE PROCEDURE `sp_get_user_status_list`(
    IN p_search_term VARCHAR(100),
    IN p_page_num INT,
    IN p_page_size INT,
    IN p_sort_column VARCHAR(50),
    IN p_sort_direction VARCHAR(4),
    IN p_group_id BIGINT
)
BEGIN
  DECLARE v_offset INT;
  DECLARE v_order_by_clause VARCHAR(255);
  DECLARE v_where_clause TEXT;
  DECLARE v_sql TEXT;
  SET @p_search_like = NULL;

  IF p_search_term IS NOT NULL AND p_search_term != '' THEN
    SET @p_search_like = CONCAT('%', p_search_term, '%');
  END IF;

  IF UPPER(p_sort_direction) NOT IN ('ASC', 'DESC') THEN SET p_sort_direction = 'ASC'; END IF;
  IF p_page_num IS NULL OR p_page_num < 1 THEN SET p_page_num = 1; END IF;
  IF p_page_size IS NULL OR p_page_size < 1 THEN SET p_page_size = 20; END IF;
  SET v_offset = (p_page_num - 1) * p_page_size;

  SET v_order_by_clause = CASE p_sort_column
    WHEN 'name' THEN CONCAT('ORDER BY user_display_name ', p_sort_direction)
    WHEN 'email' THEN CONCAT('ORDER BY u.email_address ', p_sort_direction)
    WHEN 'group' THEN CONCAT('ORDER BY g.group_name ', p_sort_direction)
    WHEN 'assets' THEN CONCAT('ORDER BY assigned_asset_count ', p_sort_direction)
    ELSE 'ORDER BY user_display_name ASC'
  END;
  IF p_sort_column != 'name' THEN
     SET v_order_by_clause = CONCAT(v_order_by_clause, ', u.user_id ASC');
  END IF;

  SET v_where_clause = 'WHERE 1=1 ';
  IF @p_search_like IS NOT NULL THEN
    SET v_where_clause = CONCAT(v_where_clause, 'AND (u.user_name LIKE @p_search_like OR u.email_address LIKE @p_search_like OR g.group_name LIKE @p_search_like) ');
  END IF;
  IF p_group_id IS NOT NULL THEN
    SET v_where_clause = CONCAT(v_where_clause, 'AND u.group_id = ? ');
  END IF;

  SET v_sql = CONCAT(
    'SELECT SQL_CALC_FOUND_ROWS ',
    '  u.user_id, ',
    '  CASE WHEN u.user_name IS NOT NULL AND u.email_address IS NOT NULL THEN CONCAT(u.user_name, \' [\', LOWER(SUBSTRING_INDEX(u.email_address, \'@\', 1)), \']\') WHEN u.user_name IS NOT NULL THEN u.user_name ELSE u.email_address END AS user_display_name, ',
    '  u.email_address, ',
    '  g.group_name, ',
    '  COUNT(r.resource_id) AS assigned_asset_count ',
    'FROM user_info u ',
    '  LEFT JOIN group_info g ON u.group_id = g.group_id ',
    '  LEFT JOIN resource_info r ON u.user_id = r.user_id ',
    v_where_clause,
    'GROUP BY u.user_id, u.user_name, u.email_address, g.group_name ',
    v_order_by_clause,
    ' LIMIT ? OFFSET ?'
  );

  PREPARE stmt FROM v_sql;
  IF p_group_id IS NOT NULL THEN
    EXECUTE stmt USING p_group_id, p_page_size, v_offset;
  ELSE
    EXECUTE stmt USING p_page_size, v_offset;
  END IF;

  SELECT FOUND_ROWS() AS total_count;
  DEALLOCATE PREPARE stmt;
END
$$

DELIMITER ;