/* Copyright (c) 2019, 2025, Oracle and/or its affiliates.

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License, version 2.0,
   as published by the Free Software Foundation.

   This program is designed to work with certain software (including
   but not limited to OpenSSL) that is licensed under separate terms,
   as designated in a particular file or component or in included license
   documentation.  The authors of MySQL hereby grant you an additional
   permission to link the program and your derivative works with the
   separately licensed software that they have either included with
   the program or referenced in the documentation.

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License, version 2.0, for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA */

#include "sql/dd/impl/system_views/applicable_roles.h"

namespace dd::system_views {

const String_type &Applicable_roles::cte_expression() {
  static String_type const s_cte_expression(
      "WITH RECURSIVE role_graph (c_parent_user, c_parent_host,"
      "                                 c_from_user, c_from_host,"
      "                                 c_to_user, c_to_host, role_path,"
      "                                 c_with_admin, c_enabled) AS "

      // Get current user as seed 1 for CTE
      "  ((SELECT "
      "      INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), "
      "      INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), "
      "      CAST('' as CHAR(64) CHARSET utf8mb4), "
      "      CAST('' as CHAR(255) CHARSET utf8mb4), "
      "      CAST(SHA2(CONCAT(QUOTE(INTERNAL_GET_USERNAME()),'@', "
      "                       QUOTE(INTERNAL_GET_HOSTNAME())), 256) "
      /*
        The use of CHAR(17000) enables use of MEDIUMTEXT datatype for
        'role_path' column.

        The CAST function uses value 'size' in CHAR(<size>) to determine
        the resulting type of CAST() function. We use size as 17000 to
        enable optimizer to pick MEDIUM TEXT. e.g.,

        CREATE VIEW v1 AS WITH RECURSIVE role1 (c1, c2) as
        (SELECT CAST('' as CHAR(16383) CHARSET utf8mb4),
                CAST('' as CHAR(16400) CHARSET utf8mb4))
        SELECT * FROM role1 WHERE c1 != '';
        SHOW COLUMNS FROM v1;
        Field Type       Null  Key  Default Extra
        c1    text       YES        NULL
        c2    mediumtext YES        NULL

        Using TEXT enable about ~2k SHA2 values to be stored in
        'role_path'. And using MEDIUMTEXT enables us to hold about ~500k
        SHA2 values.
      */
      "           AS CHAR(17000) CHARSET utf8mb4), "
      "      CAST('N' as CHAR(1) CHARSET utf8mb4), "
      "      FALSE "

      /*
         Fetch all mandatory role names which are not be assigned to
         current user, as seed 2 for CTE
      */
      "    UNION "
      "      SELECT "
      "        INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), "
      "        ROLE_NAME, ROLE_HOST, "
      "        INTERNAL_GET_USERNAME(), INTERNAL_GET_HOSTNAME(), "
      "        CAST(SHA2(CONCAT(QUOTE(ROLE_NAME),'@', "
      "                  CONVERT(QUOTE(ROLE_HOST) using utf8mb4)), 256) "
      "             AS CHAR(17000) CHARSET utf8mb4), "
      "        CAST('N' as CHAR(1) CHARSET utf8mb4), "
      "        FALSE "
      "      FROM JSON_TABLE(INTERNAL_GET_MANDATORY_ROLES_JSON(),"
      "            '$[*]' COLUMNS ("
      "            ROLE_NAME VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_NAME', "
      "            ROLE_HOST VARCHAR(255) CHARSET utf8mb4 PATH '$.ROLE_HOST') "
      "            ) mandatory_roles "
      "      WHERE CONCAT(QUOTE(ROLE_NAME),'@', "
      "                   CONVERT(QUOTE(ROLE_HOST) using utf8mb4)) NOT IN "
      "            (SELECT CONCAT(QUOTE(FROM_USER),'@', "
      "                       CONVERT(QUOTE(FROM_HOST) using utf8mb4)) "
      "             FROM mysql.role_edges "
      "             WHERE TO_USER = INTERNAL_GET_USERNAME() AND "
      "             CONVERT(TO_HOST using utf8mb4) = INTERNAL_GET_HOSTNAME())"
      "   ) "

      // Recursive CTE SELECT query
      "  UNION "
      "    SELECT c_parent_user, c_parent_host, "
      "      FROM_USER, FROM_HOST, TO_USER, TO_HOST, "
      /*
        Pass NULL for role_path to stop recursive CTE execution, once
        we locate a role that is already in the currently visited
        role_path.
      */
      "      IF(LOCATE(SHA2(CONCAT(QUOTE(FROM_USER),'@', "
      "                     CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256), "
      "                role_path) = 0, "
      "         CONCAT(role_path,'->', SHA2(CONCAT(QUOTE(FROM_USER),'@',"
      "           CONVERT(QUOTE(FROM_HOST) using utf8mb4)), 256)), NULL), "
      "      WITH_ADMIN_OPTION, "
      "      IF(c_enabled OR "
      "       INTERNAL_IS_ENABLED_ROLE(FROM_USER, FROM_HOST), TRUE, FALSE) "
      "    FROM mysql.role_edges, role_graph "

      "    WHERE TO_USER = c_from_user AND "
      "          CONVERT(TO_HOST using utf8mb4)= c_from_host AND "
      "          role_path IS NOT NULL)");
  return s_cte_expression;
}

const Applicable_roles &Applicable_roles::instance() {
  static auto *s_instance = new Applicable_roles();
  return *s_instance;
}

Applicable_roles::Applicable_roles() {
  m_target_def.set_view_name(view_name());

  m_target_def.add_distinct();

  m_target_def.add_cte_expression(cte_expression());

  m_target_def.add_field(FIELD_USER, "USER", "c_parent_user");
  m_target_def.add_field(FIELD_HOST, "HOST", "c_parent_host");
  m_target_def.add_field(FIELD_GRANTEE, "GRANTEE", "c_to_user");
  m_target_def.add_field(FIELD_GRANTEE_HOST, "GRANTEE_HOST", "c_to_host");
  m_target_def.add_field(FIELD_ROLE_NAME, "ROLE_NAME", "c_from_user");
  m_target_def.add_field(FIELD_ROLE_HOST, "ROLE_HOST", "c_from_host");
  m_target_def.add_field(FIELD_IS_GRANTABLE, "IS_GRANTABLE",
                         "IF(c_with_admin = 'N', 'NO', 'YES')");
  m_target_def.add_field(
      FIELD_IS_DEFAULT, "IS_DEFAULT",
      " (SELECT IF(COUNT(*), 'YES', 'NO') "
      "   FROM mysql.default_roles "
      "   WHERE DEFAULT_ROLE_USER = c_from_user AND "
      "         CONVERT(DEFAULT_ROLE_HOST using utf8mb4)= c_from_host AND "
      "         USER = c_parent_user AND "
      "         CONVERT(HOST using utf8mb4) = c_parent_host) ");
  m_target_def.add_field(
      FIELD_IS_MANDATORY, "IS_MANDATORY",
      "IF(INTERNAL_IS_MANDATORY_ROLE(c_from_user, c_from_host), 'YES', 'NO') ");

  m_target_def.add_from("role_graph");

  // Filter out the seed row that represent current user from the output.
  m_target_def.add_where("c_to_user != ''");
}

}  // namespace dd::system_views
